Reputation: 99
Is there a formula to extract all unique terms in a list in Excel, then sort them by the number of times they occurred in the list?
List Extracted Data
1 AA AA
2 BB CC
3 CC BB
4 AA EE
5 DD DD
6 EE
7 AA
8 CC
9 CC
10 EE
11 BB
12 AA
AA occurs 4 times, so it is placed at the top of the extracted list, followed by CC which occurred 3 times, and so on.
Upvotes: 1
Views: 10817
Reputation: 71598
I think that the faster way is by using pivot tables:
Select the table (make sure there's a header; insert one if there aren't any) and go to "Insert" > "Pivot Table". You should get something like this:
Click 'OK' then drag the header from the right pane once into "Row labels" and a second time into "Values":
Now select column B and insert a filter through "Home" > "Sort&Filter":
Now, just sort by descending:
Upvotes: 3
Reputation: 96791
First use Advanced Filter to extract the unique records to column B. Then in C1 enter:
=COUNTIF(A$1:A$12,B1) and copy down
Finally sort cols B & C by C Descending
Upvotes: 1