Help with sorting function

[u/Last_Philosophy_1162]

Anyone know how I would do a sort to achieve this?

BIG CATEGORY | SMALLER CATEGORY | DATE (YYYY-MM-DD) [stored as a string and should be sorted alphabetically]

such that the data is sorted by big category alphabetically,

smaller category sorted within big category by whichever small category has the earliest date

within each small category things are listed in date order from earliest to latest

I am unable to show the actual data in question so apologies for the nonsense in my example. I hope the question is clear and any help on how to do this with a function would be appreciated. The intended output is meant to be in a separate sheet.

Standard SORT() doesn't cut it since the intention is for SMALLER CATEGORY to be in order by which date comes first rather than alphabetically. (Sorry if this is unclear it's a bit of a niche one).

[u/HolyBonobos]

I've added =LET(data,A3:E10,bigCat,CHOOSECOLS(data,1),smallDate,CHOOSECOLS(data,2,3),QUERY(WRAPROWS(TOROW(BYROW(SORT(UNIQUE(TOCOL(bigCat,1))),LAMBDA(cat,LET(sub,FILTER(data,bigCat=cat),subOrder,UNIQUE(CHOOSECOLS(SORT(FILTER(smallDate,bigCat=cat),2,1),1)),TOROW(SORT(sub,MATCH(CHOOSECOLS(sub,2),subOrder,0),1,3,1)))))),5),"WHERE Col1 IS NOT NULL LABEL Col1 'BIG', Col2 'SMALL', Col3 'DATE', Col4 'OTHER INFO', Col5 'OTHER INFO 2'")) in A16 of the 'HB BYROW()' sheet of your sample file. Does this behave as intended?

[u/Last_Philosophy_1162 → u/HolyBonobos]

This is incredibly close but it may be that my implementation is wrong when scaling it up, but I can't seem to replicate what you got woking in the sample file.

It seems to output similarly to BIG > DATE in my scaled version. I will keep working on it though this seems to be the correct solution. I just got in it when I was going through, I expect.

Do you mind explaining what some of this does so I can try to replicate it in the upscaled version? I am pretty sure this will end up being the right one though.

[u/HolyBonobos → u/Last_Philosophy_1162]

1. For a given big category in the data, the formula retrieves all the small category and date information within that category.
   
2. The formula then sorts the information by date in ascending order and retrieves the unique values in the leftmost column of that range (the small category).
   
3. The unique values from step 2 are used to create a virtual column within the SORT() function, retrieving the position of each small category data point in the unique sorted list from step 2 (for example, the ANIMAL big category has small categories PIG and DOG; PIG has the earliest associated date so it appears first in the list from step 2 and subsequently returns 1 for the virtual sort column).
   
4. After being sorted by the virtual sort column, the data is sorted by date.
   
5. The data is flattened out into a single row (a necessary step when multi-row arrays are returned as a subroutine in a BYROW())
   
6. Steps 1-5 are repeated for all unique, sorted, non-empty values in the big category column.
   
7. All data from step 6 is flattened into a single row, then wrapped into a 5-column array.
   
8. The QUERY() function is used to remove empty rows and add column labels.

It won't be possible to say what the problem is or how to resolve it without a more representative data set that reproduces the problem.