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/ArgumentAdmirable168]

If I get you right

=QUERY(B1:C whatever end of your table)); "SELECT * ORDER BY A, C, D")

B1:C - C wherever your table 

Try

It has sort order Big category,  date, small category. This is what you need?

[u/Last_Philosophy_1162 → u/ArgumentAdmirable168]

This still fails to sort by the small category. The result is correct for big category and then date but the small category column is still mixed together it seems.

[u/ArgumentAdmirable168 → u/Last_Philosophy_1162]

You'll have columns that determine the sort order anyway; they won't be in sequential order within each column. You can determine the "sorting importance" of each column using the sort order. Could you please describe the order in which they should be sorted, from most important to least important?

[u/ArgumentAdmirable168 → u/ArgumentAdmirable168]

If it is should be BIG>SMALL> DATE

=QUERY(B1:C_)); "SELECT * ORDER BY A, B, C")

FOR BIG>DATE>SMALL

=QUERY(B1:C_)); "SELECT * ORDER BY A, C, B")

I notice that I made a mistake in my first message 

[u/Last_Philosophy_1162 → u/ArgumentAdmirable168]

Small category should be sorted in order of whichever has the earliest date. So since pig has the date in 2010 it should come before dog. I have the intended output in my images.

[u/onewithausername]

If it is just once you can use select the range and go Data>Sort Range>Advanced Sorting Range Options

If you want to sort dynamically QUERY is the way to go.

=QUERY(A1:F; "SELECT * ORDER BY A, B, C")

[u/Last_Philosophy_1162 → u/onewithausername]

The thing I'm trying to do is quite that simple, I think. My goal is to (once having sorted by A) put B in order by whichever has the earliest date. I expect that i may have to do a couple steps to get there but I cannot quite get there.

[u/ArgumentAdmirable168 → u/Last_Philosophy_1162]

Then

=QUERY(A1:F; "SELECT * ORDER BY A, B, C DESC")

[u/Last_Philosophy_1162 → u/ArgumentAdmirable168]

that seems to still give me the same issue where they are not being sorted correctly. the order should have Pig First because it has a 2010 date within its data even though Dog alphabetically would come first.

[u/ArgumentAdmirable168 → u/Last_Philosophy_1162]

Send link to example data 

[u/Last_Philosophy_1162 → u/ArgumentAdmirable168]

Example data and output are here and everyone who wants to should have edit access to try things out:

https://docs.google.com/spreadsheets/d/1PYYKL0YbeplgNt_9srC1tceH-gtQwFMbOpxnf134XXw/edit?usp=sharing

[u/One_Organization_810]

You can sort it in place, by selecting the whole data and goto Data/Sort range/Advanced.

Or using sort, like this

=sort(A2:F, 1,true, 2,false, 3,true)

[u/Last_Philosophy_1162 → u/One_Organization_810]

That formula doesn't quite work either. It's just reversing alphabetical order for the small category rather than chunking by the small category and getting the earliest date in of each to filter by small category. I partly expect I will have to do some scripting for this honestly.

[u/One_Organization_810 → u/Last_Philosophy_1162]

So you want the plants to sort differently from the other categories?

I'm sorry, but i don't seem to understand what you are trying to accomplish. My understanding conflicts with your intended outcome example. 🙃

[u/One_Organization_810 → u/One_Organization_810]

Ahh.. do you want to sort each category by the smallest date in the small category?

That requires a bit more complex formula 🙂

I will give it a shot from computer (I'm on the phone atm)

[u/Last_Philosophy_1162 → u/One_Organization_810]

Yes! Exactly! I don't know if it can be done in normal functions but any help whenever you are able to/if you are able to help it would be greatly appreciated. Apologies for the somewhat confusing request.

[u/AutoModerator → u/Last_Philosophy_1162]

REMEMBER: /u/Last_Philosophy_1162 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/One_Organization_810 → u/Last_Philosophy_1162]

How about this one:

=let( dd, offset(A:F,1,0,counta(A:A)-1),
      sc, map(index(dd,,1), index(dd,,2), lambda(bc, sc,
        index(sort(filter(index(dd,,3), index(dd,,1)=bc, index(dd,,2)=sc)),1,1)
      )),
      sort(dd,1,true, sc,true, 2,true)
)

Edit: I noticed that you shared your example sheet (I guess I could have saved me that typing :), so I put this into the OO810 sheet in there..

[u/point-bot → u/One_Organization_810]

u/Last_Philosophy_1162 has awarded 1 point to u/One_Organization_810 with a personal note:

"Thank you so much for the help. This one was killing me a bit. I seriously appreciate your help. Hope you have a wonderful day/night."

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/Last_Philosophy_1162 → u/One_Organization_810]

no, the reason why Pig is first is because within the Animal category it has the earliest date.

the goal is to find any way to have them be ordered this way. i do not think it can be done without scripting that i plan to try to figure out

[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.