Beginner excel formula question

[Marcell1992]

Good day,

I have an excel sheet with user data in it. It consumes quite a bit of time to manually count it, so I'm looking for a formula to do it quickly.

Each data entry, lets say column A, has a user assigned to it, lets say column B. Pretty sure column A is not needed for this.
I would like to identify the top 5 users most common in column B and their total. Eg.:
Bob - 5
Joe - 4
Anna - 4
Sally - 3
Bella - 2

Is this possible in excel? Pretty sure anything is possible in Excel with the right formulas.

 

[Recallza]

Create a pivot table in excel by going to the insert tab, and selecting Pivot table and order it accordingly.

 

[Nithan15]

Filter - highest to lowest?

 

[Paul Bedford]

=COUNTIF(B1:B60,"Bob")
Repeat for each name.

 

[Tabac]

Create a formula. Voila.

 

[saor]

Hmmm, not sure how different excel is from google sheets, but in sheets one way to do it is to re-order the data like this...

In cell D3 the following:
=SORT(A2:B100, A2:A100, FALSE, 2, FALSE)

 

[ArmatageShanks]

=COUNTIF(B1:B60,"Bob")
Repeat for each name.

^This

Then just sort high to low

 

[Marcell1992]

=COUNTIF(B1:B60,"Bob")
Repeat for each name.

I have 250 names, doesn't seem like a solid solution to repeat the formula 250 times. For 10 people, yea sure.

 

[newby_investor]

You can just sort the sheet by column B. Seems the simplest to me. Or is there something more complex you need to do? Does each name have only one line? Or could the name appear more than once?

 

[ArmatageShanks]

I have 250 names, doesn't seem like a solid solution to repeat the formula 250 times. For 10 people, yea sure.

If you have all the names in a row, use the same formula but change "bob" to the cell adjacent that contains the name, then just extend the formula all the way down the list, you can do this with your mouse.

 

[Suspect99]

Create a pivot table in excel by going to the insert tab, and selecting Pivot table and order it accordingly.

This is the solution.

 

[Recallza]

If you have all the names in a row, use the same formula but change "bob" to the cell adjacent that contains the name, then just extend the formula all the way down the list, you can do this with your mouse.

For this solution I'd recommend locking the cell selection range, eg. =COUNTIF($A$1:$A$250;A1) - Not the best as you'll have duplicate values for each name that is mentioned more than once.

 

[Marcell1992]

You can just sort the sheet by column B. Seems the simplest to me. Or is there something more complex you need to do? Does each name have only one line? Or could the name appear more than once?

I can sort by name, but not the result I'm looking for.

Here's an extremely small example of data:
A - Bob
B - Bob
C - Sally
D - Bob
E - Derp

Return result after formula:
Bob x3
Sally x1
Derp x1
4th most
5th most

 

[newby_investor]

Ah, I get it now. I'm not sure how to get that exactly in Excel, but in a scripting language like Python it would be pretty trivial (if you have that option).

This isn't *quite* what you want, it won't remove duplicates, but it might help you in the right direction:
https://www.extendoffice.com/documents/excel/1551-excel-sort-by-frequency.html

 

[Marcell1992]

Ah, I get it now. I'm not sure how to get that exactly in Excel, but in a scripting language like Python it would be pretty trivial (if you have that option).

This isn't *quite* what you want, it won't remove duplicates, but it might help you in the right direction:
https://www.extendoffice.com/documents/excel/1551-excel-sort-by-frequency.html

Ah thanks, I was too blind to see you can sort by frequency in excel xD