Calculating which name occurs most in a column

[kreases]

Hi,

I have catalogued my record collection and wondered if there is any way to find out which Artist's name occurs most in the Artists column, I don't require a count as such I just want to put the name that occurs most in another cell for stats purposes, any ideas?

 

[kreases]

Alan, sorry I couldn't get yours to work.

Barry, yours worked but like Alan's original link it only returns the first Artist if there are more than one tied.

Thanks for trying but I don't want to waste too much time on this it would have just been a nice stat to display.

 

[AlanY]

Alan, sorry I couldn't get yours to work.

Barry, yours worked but like Alan's original link it only returns the first Artist if there are more than one tied.

Thanks for trying but I don't want to waste too much time on this it would have just been a nice stat to display.

you have entered with Ctrl+Shift+Enter, haven't you?

 

[kreases]

Yes I had

 

[barry houdini]

Barry, yours worked but like Alan's original link it only returns the first Artist if there are more than one tied.

That's not the result that I get, it should show artists in descending order of appearance, so for your example it will show both Bee Gees and Al Green, followed by any artists with 7 appearances, then 6 etc....etc

 

[kreases]

Well perhaps someone can tell me what I am getting wrong in the transformation as because of other columns I am having to insert this formula in Column S starting at S3 and the Column which has the Artists names in is Column A (the Artists run from A3 to A614.

=INDEX($A$3:$A$614,MATCH(LARGE(FREQUENCY(MATCH($A$3:$A$614,$A$3:$A$614,0),ROW($A$3:$A$614)-ROW($A$3)+1)+ROW($A$3:$A$614)/10000,ROWS(S3:S$614)),FREQUENCY(MATCH($A$3:$A$614,$A$3:$A$614,0),ROW($A$3:$A$614)-ROW($A$3)+1)+ROW($A$3:$A$614)/10000,0))

When I ctrl shift and enter I am just getting #N/A

 

[István Hirsch]

Give this a try:

Excel Workbook
	A	B	C
1
2	Who	Who	Omega
3	Procul Harum		Who
4	Who	Who
5	Wanderers
6	Omega	Omega
7	Jimmy Hendrix
8	Omega	Omega
Sheet1

 

[kreases]

This method seems to require two more columns when I simply want to know the artist that is occurring most and if there is a tie to name all of those tied and the name(s) to be put in a cell(s) of my choosing.

I would have liked this automated but it seems it would be easier just to count them and put the names in the cells manually.

Thanks to everyone who has posted towards this, sorry I wasted your time.

 

[FormR]

Here is another option, it lists just the ties starting in C3 and down - commit with control+shift+enter and copy down as many rows as there may be possible ties.

Excel 2013/2016
	A	B	C
3	The Beatles		The Beatles
4	The Smiths		The Smiths
5	The Beatles
6	The Smiths
7	The Doors
8	The Beatles
9	The Doors
10	The Cranberries
11	The Smiths
12	The Cranberries
Sheet1

Cell Formulas
Range	Formula
C3	{=IFERROR(INDEX($A$3:$A$614,INDEX(MODE.MULT(IF($A$3:$A$614<>"",MATCH($A$3:$A$614,$A$3:$A$614,0))),ROWS(C$3:C3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kreases]

Again, when I copy down this formula I get the same answer in each cell, the Bee Gees which is the first max count, it doesn't seem to pick up the tie (Al Green), I have even copied just the artist into a blank worksheet and applied the formula but again when I copy the formula down it just repeats the first answer.

 

[FormR]

Again, when I copy down this formula I get the same answer in each cell, the Bee Gees which is the first max count, it doesn't seem to pick up the tie (Al Green), I have even copied just the artist into a blank worksheet and applied the formula but again when I copy the formula down it just repeats the first answer.

I'd suggest that there is somthing wonky with your data then. Maybe some of the "Bee Gees" have trailing spaces or other hidden characters and are tied with each other

If you do something like =COUNTIF($A$3:$A$614,"Bee Gees") - do you get the count you expect?

If you can't figure it out then maybe you could remove all the data except for A3:A614 and your attempted formulas and put the workbook on a file sharing site (like dropbox) and share the link here so someone can take a look.