Forum Discussion
Sorting a list based on number of times a word appears in a list
Hi,
I have a list containing specific words (all repeated at various times). I know how to sort alphabetically, but I then need them sorted and grouped by the number of times each word appears in the list. For example:
Original list:
banana
orange
cherry
lime
lime
orange
banana
banana
banana
orange
The sorted list should look like the following:
banana
banana
banana
banana
banana (in original list 5 times, so shows up 1st or last)
orange
orange
orange (shows up in original list 3 times)
lime (shows up in original list 2 times)
lime
cherry (only shows up once)
Or the list could be reversed, with "cherry" first and "banana" last, based on the number of times the word shows up in the list.
The list can be either ascending or descending, it does not matter - just so long as it "groups" the words in the list by the number of times it shows up once the list is sorted alphabetically. I can't figure a formula given those parameters. Using Office 2019 in Windows 10 Pro.
Thanks!
5 Replies
- PeterBartholomew1Silver Contributor
Because Excel 365 is intrinsically array based, the formula
= SORTBY(Fruit, COUNTIFS(Fruit, Fruit), -1)
works without helper ranges or manual sorting.
- SergeiBaklanDiamond Contributor
PeterBartholomew1 , that's 2019
- DebbieLMCopper ContributorAnd another possibility - thank you!
I'd use a helper column. The formula in B1 is =COUNTIF($A$1:$A$10,A1) and this has been filled down.
Sort on column B, largest to smallest:
- DebbieLMCopper Contributor