Return the top 10 occurrences of an item in a table and the count of that item

[Tipsey]

Hello everyone,

I have a data table that I would like to use a filter function on. I would like to filter out and show the 10 most frequent employee numbers that show up in the table, along with their name and how many times they were in the list. I am struggling to complete this formula.

=LARGE(COUNTIF(Table3[Emp '#],UNIQUE(Table3[Emp '#],FALSE,FALSE)),{1;2;3;4;5;6;7;8;9;10})

This is accurately returning the count of the top 10 employee numbers to show up, but I am having a hard time turning that around and finding out who was in the list 10 times etc.

=SORT(HSTACK(UNIQUE(FILTER(Table3[Emp '#],Table3[Emp '#]),FALSE,FALSE),XLOOKUP(UNIQUE(FILTER(Table3[Emp '#],Table3[Emp '#]),FALSE,FALSE),Table3[Emp '#],Table3[Emp Name]),COUNTIF(Table3[Emp '#],UNIQUE(FILTER(Table3[Emp '#],Table3[Emp '#]),FALSE,FALSE))),3,-1)

This grotesque formula gives me the entire list and sorts it, which isn't terrible, and I may use it, but I would like to be able to find just the top 10. Also if somebody has a cleaner way of creating that second formula I would be interested in seeing that too.

Thanks in advance for any help you can provide and for taking the time to do so.

Cheers

 

[Fluff]

How about

=LET(u,UNIQUE(Table3[Emp '#]),TAKE(SORTBY(u,COUNTIFS(Table3[Emp '#],u),-1),10))

 

[Tipsey]

My two favorite words to read after posting a question on here.

Thank you very much for the response, and frankly all of the responses to everyone's inquiries.

 

[Fluff]

You're welcome & thanks for the feedback.