Jul 19 2022 08:30 AM
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! :)
Jul 19 2022 08:47 AM
Jul 19 2022 08:59 AM
SolutionThis 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.
Jul 19 2022 09:54 AM
Aug 31 2022 02:41 PM
Sep 01 2022 08:10 AM
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
dog | dog | Data |
nose | nose | 1 |
ears | ears | 2 |
paws | paws | 4 |
cat | cat | |
nose | nose | 1 |
ears | ears | 2 |
paws | paws | 4 |
fish | fish | |
nose | nose | 0 |
ears | ears | 0 |
paws | paws | 0 |
Then what I did was drag down the "titles" to overlap over the subtasks so it looked like this:
dog | dog | Data |
dog | nose | 1 |
dog | ears | 2 |
dog | paws | 4 |
cat | cat | |
cat | nose | 1 |
cat | ears | 2 |
cat | paws | 4 |
fish | fish | |
fish | nose | 0 |
fish | ears | 0 |
fish | paws | 0 |
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.
Jul 19 2022 08:59 AM
SolutionThis 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.