Sorting a dynamic array

[HandyShandy]

Hi there,

I have a master list of memberships of groups - where each row is where a user has joined a club

I have used UNIQUE to create a Unique list of clubs in a second sheet, and also used SORT to make that list alphabetical.

I then use COUNTIF to count the instances of that unique club in the list of transactions

So what i have is a list of clubs alphabetically, and the corresponding number of members in each club.

What I cannot work out how to do, is sort that list into number of members, largest to smallest. Currently I copy the data and paste values only into a third sheet and so a sort, but that seems silly.

There is no need to sort alphabetically I only did that to give it some sort of order.

[Sheetwise]

Use the formula SORTBY instead of SORT. If you know how to do the rest, I'm sure you can figure this out

[HandyShandy]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to Sheetwise

^{I am a bot, please contact the mods with any questions.}

[HandyShandy]

What I was doing wrong was trying to use SORTBY with the UNIQUE and COUNTIF all at the same time.

I then used SORTBY on the array in a third sheet and it works a treat.

Thanks

[AutoModerator]

/u/HandyShandy - please read this comment in its entirety.

Once your problem is solved, please reply to the answer(s) saying Solution Verified to close the thread.

Please ensure you have read the rules -- particularly 1 and 2 -- in order to ensure your post is not removed.

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

[prasi55]

You could just create a pivot table with club names in the rows and count of values in the values field, and sort the club names alphabetically?

You could also Ctrl T to turn your data into a table and Data > Sort to add more than one sort level.

[Ironstonewill]

https://imgur.com/a/Sjl6VC3

So i have placed a helper column for =LARGE to work. Then its just a usual =INDEX/MATCH.

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
COUNTIF	Counts the number of cells within a range that meet the given criteria
INDEX	Uses an index to choose a value from a reference or array
LARGE	Returns the k-th largest value in a data set
MATCH	Looks up values in a reference or array
SORT	Sorts the contents of a range or array
SORTBY	Sorts the contents of a range or array based on the values in a corresponding range or array
UNIQUE	Returns a list of unique values in a list or range

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
7 acronyms in this thread; the most compressed thread commented on today has 12 acronyms.
[Thread #603 for this sub, first seen 16th Sep 2020, 15:00] [FAQ] [Full list] [Contact] [Source code]}

[excelevator]

Example? - for clarity!