Find top 3 repeated values in a column

[hossam sadek]

I have Column A Contain a list of repeated names and I want to find a top 3 repeated names in the list based on ranking 1 - 2 - 3

 

[Peter Bartholomew]

My solution would be

= LET(
  distinct, UNIQUE(names),
  count,    COUNTIFS(names, distinct),
  ordered,  SORTBY(distinct,  count),
  INDEX(ordered, {1;2;3}))

but I only develop for 365.

 

[hossam sadek]

@Peter Bartholomew Thanks Alot for your help , is it possible to provide an example of the formula on this sheet i still can't imagine the layout
i need to be able to find top 3 names in Column A and be placed next to 1 - 2 - 3

Untitled spreadsheet

Sheet1 Top 3 Names,1 2 3 Delta Charlie Delta Delta Alpha Charlie Bravo Alpha Delta Charlie Delta Charlie Delta Charlie Delta Charlie Delta Charlie Delta Charlie Delta Charlie Delta

docs.google.com

 

[Peter Bartholomew]

All that is needed is to apply the name "names" to the list in column A and place the formula in any cell on any sheet.
It does rely on your using Excel 365 though.

 

[John Jairo V]

Hi to both!

Another way, with Microsoft 365 insider beta (similar to @Peter Bartholomew approach) :

=LET(u,UNIQUE(names),
c,COUNTIFS(names,u),
s,SORTBY(u,c,-1),
TAKE(s,3))

Blessings!

 

[Peter Bartholomew]

Hi to both!

Another way, with Microsoft 365 insider beta (similar to @Peter Bartholomew approach) :

TAKE(s,3))

That brings it up to date. I only got the function yesterday!

 

[deciog]

John Jairo V and Peter Bartholomew, Good morning.

In my Excel 365 Portuguese version (Brazil) there are also these new formulas, I would like to know how you know that these new functions came out, because here it does not inform but it has this function

Thank you in advance for your experience which is helping a lot

A big hug

Decio

 

[Peter Bartholomew]

Sorry, @decio, the answer is not impressive. Each morning, you settle down with a coffee, put Excel up, and start to type a formula beginning with one of the new functions. The outcome is disappointment then, one day, excitement.