CODEWITHSUNDEEP
excelsortinguniqueextractformula
Quorrin
Quorrin

Reputation: 99

Extracting unique values from a list and sorting by occurrence in Excel

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

Answers (2)

Jerry
Jerry

Reputation: 71598

I think that the faster way is by using pivot tables:

  1. 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: enter image description here

  2. Click 'OK' then drag the header from the right pane once into "Row labels" and a second time into "Values": enter image description here

  3. Now select column B and insert a filter through "Home" > "Sort&Filter": enter image description here

  4. Now, just sort by descending: enter image description here

Upvotes: 3

Gary's Student
Gary's Student

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

Related Questions