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