Sorting Main Categories and Associated Sub-Catagories

[Kalkul8]

I'm trying to create a list with both main categories and associated sub-categories that can be sorted, occasionally. I've attached a draft example (I hope!). If I sort my table on the basis of the first column as the first level and the second column as the second level, I end up with items in the second column being disassociated with their first column groups.

Is there a way of 'locking' items in the second column to their correct first column groups so that the association is not lost on sorting?

[Richard Buttrey]

This is a new one.

We often have inadequate information, but rarely a completely empty workbook.

[Kalkul8]

Thanks for your reply Richard and for pointing out my mistake. I'll have another go at sending the workbook. I think that I have followed the manage attachements instruction. I checked in 'Preview Post' and it showed there.

[AliGW]

Don't put form before function!

Here's the easiest and best way to organise the lists (and they will filter nicely once you add filters to the column headers):

Excel 2016 (Windows) 32 bit

	A	B
3	Food Group	Food
4	Dairy	Butter spread
5	Dairy	Milk FF
6	Dairy	Milk SS
7	Dairy	Cheese - Cheddar
8	Vegetables	Potatoes
9	Vegetables	Carrots
10	Vegetables	Broccoli
11	Fruit	Apples
12	Fruit	Tangerines
13	Fruit	Bananas

Sheet: Sheet1

[Kalkul8]

Thanks, Ali that's a very simple and effective solution. Why didn't I think of that! Can you tell me a bit more about filters and how to use them, please?

[Richard Buttrey]

...and to follow up Ali's point the easiest way of achieving this is

1. Select A4:A13
2. Press F5 Goto, Select Special and then Blanks
3 . The Cursor will be in A5 (the first blank). enter "=" followed by the up arrow
4. Ctrl & Enter

[Richard Buttrey]

Thanks, Ali that's a very simple and effective solution. Why didn't I think of that! Can you tell me a bit more about filters and how to use them, please?

As well as exploring filters check out Inserting a simple Pivot Table - see attached

It may not be particularly relevant for this simple task, but where you have several columns of data and need to Filter, Sort, Total, Count...etc.. i.e.derive some analysis of the data it will provide huge dividends in the future.

[Kalkul8]

Thanks for your reply, Richard. I followed your steps and got the blanks in column A filled appropriately. This is a 'filter' that I've applied?

[Richard Buttrey]

Just select A3, Click Sort accepting the default 'Food' field, the default My data has headers, and the default A-Z Order and click OK

[Kalkul8]

...that's wrapped it up nicely Richard. Thanks to you and Ali.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.