SOLVED

Excel Table - Sorting Information in Alphabetical Order With Subdata

Copper Contributor

I have a report I need to put in alphabetical order, but each title has subtasks beneath it. I want to put all the titles (names in bold) in alphabetical order, but I want the information that goes along with each title (construction drawings, design drawings etc.) to be locked so it is not put in alphabetical order. For example, 

The names, trevor, cheryl, and kim are my titles that I want to be in alphabetical order.. but the rows beneath that are not bolded I need to keep with the name as this data is specific to the name of the person. It needs to somehow be grouped together (but the group function in my experience does not work for what I am trying to do).

How can I sort the names in alphabetical, but keep the below information in tact?

Is there some way that I can have the name drop down the information below, so it can be minimized and dropped down? The data also needs to stay with the columns to the right.

 

I have had 0 luck finding a way to do this. Please help! :)

 

 

eeftodie_0-1658244054280.png

 

5 Replies
It's do-able. Other than the bold formatting, might there be anything else in the rows with trevor/cheryl/kim that might tell us those are title rows?
best response confirmed by Hans Vogelaar (MVP)
Solution

@eeftodie 

This might work if you're open to using a formula solution:

=LET(order,SCAN("",names,LAMBDA(a,v,IF(XOR(v={"trevor","cheryl","kim"}),v,a))),SORTBY(data,order,1))

More names can be added as needed.


Please see attached workbook sample. 

Thank you so much for your help, I found an alternative way to sort the data!!!!! So happy
Can you please let me know how you did this?? Im trying so hard to do something similar

I will try to explain this as best as I can, but forgive me if it's not explained very well!!

 

How I solved this problem was by duplicating the entire column with the names and subtasks into another column to the left. After you do this, there should be two columns side by side that are exactly the same. here is an example

dogdogData
nosenose1
ears ears 2
pawspaws4
catcat 
nosenose1
ears ears 2
pawspaws4
fishfish 
nosenose0
ears ears 0
pawspaws0

 

Then what I did was drag down the "titles" to overlap over the subtasks so it looked like this:

dogdogData
dognose1
dogears 2
dogpaws4
catcat 
catnose1
catears 2
catpaws4
fishfish 
fishnose0
fishears 0
fishpaws0

after that, you can turn the data into a table and sort the data in alphabetical without messing up the subtasks. you will be sorting the duplicated column to get the titles in alphabetical.

After that's done sorting, you can delete the duplicated column.

 

This only really works if you have time to go through the data and drag down and duplicate the titles.

Let me know if you have any questions. 

@sylviawhite08 

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@eeftodie 

This might work if you're open to using a formula solution:

=LET(order,SCAN("",names,LAMBDA(a,v,IF(XOR(v={"trevor","cheryl","kim"}),v,a))),SORTBY(data,order,1))

More names can be added as needed.


Please see attached workbook sample. 

View solution in original post