Top 10 customer per category

Next time, please post a file that meets the forum guidleines: 10-20 rows and expected results, calculated manually... not 1900 rows and no expected results at all. As your sample is massive, I have done little or no manual checking.

To return the categories (and assuming that you still do not have O365):

=IFERROR(INDEX($A$2:$A$1921,MATCH(1,INDEX(--ISNA(MATCH($A$2:$A$1921,E$1:E1,)),),)),"")

copied down. To return the names, this formula will return the first 10... in frequency order... providing that they occur more than once.

=IFERROR(INDEX($B$2:$B$1921,MODE(IF(COUNTIF($E2:E2,$B$2:$B$1921)=0,IF($A$2:$A$1921=$E2,MATCH($B$2:$B$1921,$B$2:$B$1921,0))))),"")

It is an array formula in your stated version of Excel. These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

IF you want the top 10 to include names that occur ONLY once, the formula in the second block will do that... returning values with n=1 in order of appearance.

Do you have Microsoft O365?

You will struggle (without VBA) to get the exact layout you want, especially in a Pivot Table. Did you even look at the layout in the second datablock in my post??

You could use Power Query. Format your source data as a table, then use:

Please Login or Register  to view this content.