Return the most used title

[hnt007]

Hi and thanks for your help!

I have a column with track titles and I'm trying to find a formula that will return the most used track title and that would also let me know how many times it was used, like This Is a Cool Title (5)

=INDEX(A2:A1000,MODE(MATCH(A2:A1000,A2:A1000,0))) with Ctrl Shift Enter doesn't work, I get a #N/A error

 

[etaf]

i have had a play with my answer to your other question

Counting number of occurrences and returning text

Hi and thanks for helping me out! I have track titles in column A, and I would like a formula or formulas that will let me know what track titles are present in this column and the number of their occurrences. Example: A1: Track Titles A2: Cool A3: Here We Go A4: That Title A5: Cool A6: Nice...

www.mrexcel.com

and sorted the unique list and count , by order , so the most frequent title is at the top
in the last example you had 2 titles which had 2 occurrences

this uses a let function, and i found using a google search

Book1
	A	B	C	D
1	Track Titles		Title	Count
2	Cool		Cool	2
3	Here We Go		That Title	2
4	That Title		Here We Go	1
5	Cool		Nice	1
6	Nice
7	That Title
8
9
10
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=IF(LET(x,A2:A14,y,UNIQUE(x),SORTBY(y,COUNTIF(x,y),-1))=0,"",LET(x,A2:A14,y,UNIQUE(x),SORTBY(y,COUNTIF(x,y),-1)))
D2:D7	D2	=IF(C2="","",COUNTIF($A$2:$A$19,C2))
Dynamic array formulas.

the explanation of the Let function is here

How to list unique distinct values sorted by frequency

What's on this page Unique distinct values sorted based on frequency (single column) Unique distinct values sorted based on frequency - Excel 365

www.get-digital-help.com

excel 365 automatically adds an Array , so does not need to have Ctrl Shift Enter used

 

[hnt007]

This is perfect and genius! Exactly what I needed!! Thanks!!

 

[hnt007]

i have had a play with my answer to your other question

Counting number of occurrences and returning text

Hi and thanks for helping me out! I have track titles in column A, and I would like a formula or formulas that will let me know what track titles are present in this column and the number of their occurrences. Example: A1: Track Titles A2: Cool A3: Here We Go A4: That Title A5: Cool A6: Nice...

www.mrexcel.com

and sorted the unique list and count , by order , so the most frequent title is at the top
in the last example you had 2 titles which had 2 occurrences

this uses a let function, and i found using a google search

Book1
	A	B	C	D
1	Track Titles		Title	Count
2	Cool		Cool	2
3	Here We Go		That Title	2
4	That Title		Here We Go	1
5	Cool		Nice	1
6	Nice
7	That Title
8
9
10
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=IF(LET(x,A2:A14,y,UNIQUE(x),SORTBY(y,COUNTIF(x,y),-1))=0,"",LET(x,A2:A14,y,UNIQUE(x),SORTBY(y,COUNTIF(x,y),-1)))
D2:D7	D2	=IF(C2="","",COUNTIF($A$2:$A$19,C2))
Dynamic array formulas.

the explanation of the Let function is here

How to list unique distinct values sorted by frequency

What's on this page Unique distinct values sorted based on frequency (single column) Unique distinct values sorted based on frequency - Excel 365

www.get-digital-help.com

excel 365 automatically adds an Array , so does not need to have Ctrl Shift Enter used

This is perfect! Thanks!

 

[etaf]

you are welcome
As i mentioned, I have seen the new LET() function used in replies here, but not yet learnt how to use it, I was waiting for a requirement , now i have yours i will also goto the website and see how it works for this solution.

NOTE it will only work in 365 versions as far as i know, so if you share the info with people who have older versions of Excel, it will not work

interesting ,
=SORTBY(UNIQUE(A2:A14), COUNTIF(A2:A14,UNIQUE(A2:A14)),-1)
will work, as mentioned in the article

and the Let is very simple to make a shorter formula
I also think it improves performance

Book1
	A	B	C	D	E	F	G
1	Track Titles		Title	Count		Formula	Let
2	Cool		Cool	3		Cool	Cool
3	Here We Go		That Title	2		That Title	That Title
4	That Title		Here We Go	1		Here We Go	Here We Go
5	Cool		Nice	1		Nice	Nice
6	Nice					0	0
7	That Title
8	Cool
9
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=IF(LET(x,A2:A14,y,UNIQUE(x),SORTBY(y,COUNTIF(x,y),-1))=0,"",LET(x,A2:A14,y,UNIQUE(x),SORTBY(y,COUNTIF(x,y),-1)))
F2:F6	F2	=SORTBY(UNIQUE(A2:A14), COUNTIF(A2:A14,UNIQUE(A2:A14)),-1)
G2:G6	G2	=LET(x,A2:A14,y,UNIQUE(x),SORTBY(y,COUNTIF(x,y),-1))
D2:D8	D2	=IF(C2="","",COUNTIF($A$2:$A$19,C2))
Dynamic array formulas.