CODEWITHSUNDEEP
excelexcel-formula
JOD
JOD

Reputation: 37

Excel Formula - Top 10 Most repeated values in a list

I have a column of a table with many repeated values, and I want to use a function that reads that table column and returns me the top 10 most repeated values.

The values in this case are codes similar to this one "BM-YUOS-727384-9898".

Can you help me ?

Upvotes: 1

Views: 2171

Answers (3)

Javier Martin Gil
Javier Martin Gil

Reputation: 36

Good answers the above.

I'd make something simpler for myself.

  1. Copy the column you want to get the top10.

2.On that column I would apply remove duplicates (I select that column menu Data>Remove duplicates)

3.Add Top10 column, with a COUNT fomula. IF('Original Column';; Cell an appearance) that formula will return the number of repetitions of the original column for each unique value

4.I would select the two columns (Single Data, number of occurrences) would apply a filter and sort by the number of occurrences.

You have an example in xls in this link https://1drv.ms/x/s!Au5eUS7-QWgfgqQEN5AiTUfvERQJig?e=gMFDuU

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

I would go the pivot table route, but if one has the dynamic array formulas available in Office 365, this formula will bring the top 10.

=INDEX(SORT(UNIQUE(CHOOSE({1,2},A1:A367,COUNTIF(A1:A367,A1:A367))),2,-1),SEQUENCE(10),1)

enter image description here

Upvotes: 2

Jacob K
Jacob K

Reputation: 1183

If you aren't working programmatically, you can always convert the table to a Pivot Table, in which case that ability is built-in:

https://www.techonthenet.com/excel/pivottbls/top10_2016.php

Upvotes: 2

Related Questions