Nesting (and Sorting) Columns

I have some main cells with project names and for each project I have multiple subsections listing different details for each project (as shown in the photo). I'm trying to be able to sort the project names using a list but still keep all the nested details for each project in line with their respective project names.

In my photograph, A,B,C... are the project names and the repeated headings underneath each letter are the subsections. Whenever I try to sort that column, the subsections always detach from the project titles. I have tried grouping, but it seems when I try to sort grouped elements, nothing moves.

Can somebody help me?

Originally Posted by hashcel

I have some main cells with project names and for each project I have multiple subsections listing different details for each project (as shown in the photo). I'm trying to be able to sort the project names using a list but still keep all the nested details for each project in line with their respective project names.

In my photograph, A,B,C... are the project names and the repeated headings underneath each letter are the subsections. Whenever I try to sort that column, the subsections always detach from the project titles. I have tried grouping, but it seems when I try to sort grouped elements, nothing moves.

Can somebody help me?

I'd be inclined to use a helper column and ensure this contains a unique reference.

For instance against each of your values in column B starting in B2, add the values:-

A0,AA,AB,AC,AD,AE,AF,AG,AH,AZ1,AZ2

then starting in B13
C0,CA,CB,CC,CD,CE,CF,CG,CH,CZ1,CZ2

and etc., ensuring that the first cell in each block has the block reference with a zero appended, and the last two blank cells are given the block reference with 'Z1' & 'Z2' appended.

When you've done this you have created a unique ascending code for all your data. Now if you sort columns A & B, with Column B as the sort key you'll find the block B stuff sorts in the right place.

Regards

I'm confused.

My understanding is that you want me to move all my subsections (Site Preparation... Miscellaneous Fees) to column B. When you say "add the values A0,AA,AB,AC,AD,AE,AF,AG,AH,AZ1,AZ2", what do you mean exactly? Sum them?

I don't know what block references are, or how to append values.

I only now the very basics of excel, and I need a little more help.

Thanks!

I think my intentions were interpreted incorrectly. I'm trying to sort the project titles (A,C,D,B) while still keeping the subgroups (Site preparation...miscellaneous fees) in order and in line with their respective project titles. I do not want to sort the subgroupings or their values at all, I just want them to stay with the project titles when the titles are sorted.

I also want these to be sorted using a list.

Thanks for the help!

Originally Posted by hashcel

I'm confused.

My understanding is that you want me to move all my subsections (Site Preparation... Miscellaneous Fees) to column B. When you say "add the values A0,AA,AB,AC,AD,AE,AF,AG,AH,AZ1,AZ2", what do you mean exactly? Sum them?

I don't know what block references are, or how to append values.

I only now the very basics of excel, and I need a little more help.

Thanks!

Hi,
It's sometimes difficult to know in advance the experience that people have. Apologies if I've confused you.

No, I didn't suggest that you should move all your subsections, which is why I said you should use a 'helper' column. A helper column is an additional column to what you have, but which is filled with appropriate values to help your task. It's a bit like a chemical catalyst, something that speeds up, or in this case helps achieve a task, without affecting the underlying data.

Your problem, if I've understood it correctly, is that you can't sort on column A because your Names, A,B,C, when sorted disappear from the natural order. Presumably you're trying to get the 'B' names sorted back into position since this section comes below the 'D' Names.

That being the case, then we need to create a column which contains values that when sorted 'line up' as you would expect. The technique therefore is to create a unique code for every single row of your data. Once you have a unique code, which has some logic to it, then you can safely add or change data names and get them resorted back in line.

So add a new helper column A, so that your current column A now becomes Column B.

Now against every row in column A you need to add a code, and I gave you a suggestion.

For instance in A2 enter A0 (zero), in A3 enter AA, in A4 enter AB....etc. Can you see what we're doing? We're creating a new code which has a natural order when it is sorted. A zero sorts before a letter, and of course letters sort as you'd expect. Your data has two blank rows between each section, but since you'll require these once you've resorted, these too need to be given a code, which is why I suggested AZ1 for row A11, and AZ2 for A12. A Z1 will obviously always sort after the H, which is your last Prelims. item

Now do the same for the next 'C' block, but now use C0 (zero) for A13, CA for A14, CB for row 14 etc..

You could speed up this process by using the formula:

Please Login or Register  to view this content.

in cell A3 and copying it down, apart from the blank cells in column B where you'll need to manually add the AZ1, AZ2 etc.
You'll also need to alter the $B$2 to $B$13 for the next C block etc., but ignore all this last bit if it's confusing and you prefer to add all the codes manually.

Now once you've populated the new column A, you can do a normal sort on the whole of your data, including column A of course, and your sections will be back in order.

Hope I've understood your requirement. Post back if not.

Are you a QS by any chance? I spent my working career in construction and your data looks extremely familiar.

Rgds

Alright, I understand your methodology, thank you.

However, I'd rather not place a helper column in this spreadsheet due to presentation's sake. Is it possible to use the grouping function to group the respective subsections to their project title, so the spreadsheet appears nice.

Also, is it possible to to sort these using a list? (The drop down box on the header).

I really appreciate your help.

I don't know what a QS is but yes, this is construction related.

Once again, thank you.