Top 10 list

[Suarez]

Hi,

I want to create a top 10 list for each client I report on.

I have 2 columns I want to report on the first is client and the second is Category. We have 6 clients and I want to do a count of what the most popular categories for each of them are.

Any help will be appreciated.

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Suarez]

attachment included

[TMS]

Insert a Pivot Table. Add Client and Category to the Row Labels and Category to the Values; it will default to a count. Sort Largest to Smallest.

[TMS]

Oh, and you can add a filter to select the Top 10.

[TMS]

Updated Sample File:

[Suarez]

Is there a way to do it without a pivot table?

[TMS]

Well, you could maybe copy the client and category columns somewhere else and use Remove Duplicates to extract a list of unique combinations. Then you can add a helper column using COUNTIFS. Once you have your totals, you can sort largest to smallest. Not sure how you'd limit it to the Top 10 for each client.

It of hard work though for little advantage.

Maybe John will come up with an approach you prefer.

[Suarez]

No problem - thanks. I agree, I think using a pivot table will suffice

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Glenn Kennedy]

How about this? Happy to explain, if it's OK...

[TMS]

@Glenn: great way to extract the categories. Can't see how that's going to give the Top 10 for each client though. The example data doesn't have lots of clients or categories but I assum the matrix could be much bigger.

[Glenn Kennedy]

Agreed. Pivot Table is probably the best (even though it pains me to say so... as I don't like them).

[Suarez]

exactly what I want - but (sorry, I should have stated) I need this on a separate tab

[Suarez]

Is there also a way that you can automatically sort it, largest to smallest per client?

[TMS]

With the Pivot Table, you could leave the Category in the row and move the client to the Column Labels to get something similar. You can choose to put the Pivot Table on a new sheet (the default, I think)

[Glenn Kennedy]

Here it ison a separate sheet. Sorting will require a different approach. I have an idea....

[Glenn Kennedy]

Sheet 2, a2, an array formula copied down:

=IFERROR(INDEX(Sheet1!$I$3:$I$11,MODE(IF(Sheet1!$G$3:$G$11=$A$1,IF(COUNTIF($A$1:A1,Sheet1!$I$3:$I$11)=0,MATCH(Sheet1!$I$3:$I$11,Sheet1!$I$3:$I$11,0)+{0,0})))),"")

B2, copied down:

=IF(A2="","",COUNTIFS(Sheet1!G:G,$A$1,Sheet1!I:I,A2))

Array Formulae 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 curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - 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...