Create a top 10 list of most frequently used text values

[glennas94]

I want to create a top 10 list in Sheet 2 of most frequently listed artists in Sheet 1 from columns D:I

 

[SanjayGMusafir]

Just create a helper column on sheet 1 to count number of times an artist has appeared

then on sheet2 filter the results and sort descending. Simple

 

[glennas94]

Just create a helper column on sheet 1 to count number of times an artist has appeared

then on sheet2 filter the results and sort descending. Simple

Sorry, I'm still a beginner with Excel. Could you please provide show me an example specific to my attached workbook?

 

[SanjayGMusafir]

Sorry, I'm still a beginner with Excel. Could you please provide show me an example specific to my attached workbook?

What you have attached is a screenshot, can't work on that.

Upload sample data using XL2BB to work on and give you a solution.

 

[glennas94]

What you have attached is a screenshot, can't work on that.

Upload sample data using XL2BB to work on and give you a solution.

musiccollectionexample.xlsx
	D	E	F	G	H	I	J	K	L	M	N
1	Artist	Additional Artist #1	Additional Artist #2	Additional Artist #3	Additional Artist #4	Additional Artist #5	Title
2	10cc						The Very Best Of 10cc
3	The 12th Man						Wired World Of Sports
4	The 12th Man						12th Man Again!
5	The 12th Man						Bill Lawry...This Is Your Life
6	The 12th Man						The Final Dig?
7	The 12th Man						Boned!
8	2Pac	Dr. Dre					California Love
9	2Pac						Greatest Hits
10	ABBA						Ring Ring
11	ABBA						ABBA
12	ABBA						The Best Of ABBA
13	ABBA						Arrival
14	ABBA						The Visitors
15	Michael Abels						Us (Original Motion Picture Soundtrack)
16	AC/DC						Dirty Deeds Done Dirt Cheap
17	AC/DC						Back In Black
18	Across The Universe Cast						Across The Universe (Music From The Motion Picture)
19	Louise Adams						Louise Adams
20	Adele						25
21	Aerosmith						Toys In The Attic
22	Aerosmith						I Don't Want To Miss A Thing
23	a-ha						Hunting High And Low
24	Lynn Ahrens	Stephen Flaherty	David Newman				Anastasia (Music From The Motion Picture)
25	AIR						Moon Safari
26	AIR						Le Voyage Dans La Lune
27	Air Supply						Love Songs
28	Akon	Eminem					Smack That
29	Mali Music						Mali Music
30	Monkey						Journey To The West
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:P1639	Expression	=MOD(ROW(),2)=0	text	NO

 

[bebo021999]

You may need Unique Artists List also (in column P:Q), then top 10 (in S:T)

Book1
	D	E	F	G	H	I	J					O	P	Q	R	S	T
1	Artist	Additional Artist #1	Additional Artist #2	Additional Artist #3	Additional Artist #4	Additional Artist #5	Title						Unique List	Count		Top 10	Count
2	10cc						The Very Best Of 10cc						10cc	1		The 12th Man	5
3	The 12th Man						Wired World Of Sports						The 12th Man	5		ABBA	5
4	The 12th Man						12th Man Again!						2Pac	2		2Pac	2
5	The 12th Man						Bill Lawry...This Is Your Life						ABBA	5		AC/DC	2
6	The 12th Man						The Final Dig?						Michael Abels	1		Aerosmith	2
7	The 12th Man						Boned!						AC/DC	2		AIR	2
8	2Pac	Dr. Dre					California Love						Across The Universe Cast	1		10cc	1
9	2Pac						Greatest Hits						Louise Adams	1		Michael Abels	1
10	ABBA						Ring Ring						Adele	1		Across The Universe Cast	1
11	ABBA						ABBA						Aerosmith	2		Louise Adams	1
12	ABBA						The Best Of ABBA						a-ha	1
13	ABBA						Arrival						Lynn Ahrens	1
14	ABBA						The Visitors						AIR	2
15	Michael Abels						Us (Original Motion Picture Soundtrack)						Air Supply	1
16	AC/DC						Dirty Deeds Done Dirt Cheap						Akon	1
17	AC/DC						Back In Black						Mali Music	1
18	Across The Universe Cast						Across The Universe (Music From The Motion Picture)						Monkey	1
19	Louise Adams						Louise Adams
20	Adele						25
21	Aerosmith						Toys In The Attic
22	Aerosmith						I Don't Want To Miss A Thing
23	a-ha						Hunting High And Low
24	Lynn Ahrens	Stephen Flaherty	David Newman				Anastasia (Music From The Motion Picture)
25	AIR						Moon Safari
26	AIR						Le Voyage Dans La Lune
27	Air Supply						Love Songs
28	Akon	Eminem					Smack That
29	Mali Music						Mali Music
30	Monkey						Journey To The West
Sheet2

Cell Formulas
Range	Formula
P2:P23	P2	=IFERROR(INDEX($D$2:$D$30,MATCH(0,INDEX(COUNTIF($P$1:P1,$D$2:$D$30),),0)),"")
Q2:Q23	Q2	=IF(P2="","",COUNTIF($D$2:$D$30,P2)-MATCH(P2,$D$2:$D$30,0)*10^-10)
S2:S11	S2	=LOOKUP(2,1/($Q$2:$Q$23=LARGE($Q$2:$Q$23,ROWS($1:1))),$P$2:$P$23)
T2:T11	T2	=IF(S2="","",COUNTIF($D$2:$D$30,S2))

 

[glennas94]

You may need Unique Artists List also (in column P:Q), then top 10 (in S:T)

Book1
	D	E	F	G	H	I	J					O	P	Q	R	S	T
1	Artist	Additional Artist #1	Additional Artist #2	Additional Artist #3	Additional Artist #4	Additional Artist #5	Title						Unique List	Count		Top 10	Count
2	10cc						The Very Best Of 10cc						10cc	1		The 12th Man	5
3	The 12th Man						Wired World Of Sports						The 12th Man	5		ABBA	5
4	The 12th Man						12th Man Again!						2Pac	2		2Pac	2
5	The 12th Man						Bill Lawry...This Is Your Life						ABBA	5		AC/DC	2
6	The 12th Man						The Final Dig?						Michael Abels	1		Aerosmith	2
7	The 12th Man						Boned!						AC/DC	2		AIR	2
8	2Pac	Dr. Dre					California Love						Across The Universe Cast	1		10cc	1
9	2Pac						Greatest Hits						Louise Adams	1		Michael Abels	1
10	ABBA						Ring Ring						Adele	1		Across The Universe Cast	1
11	ABBA						ABBA						Aerosmith	2		Louise Adams	1
12	ABBA						The Best Of ABBA						a-ha	1
13	ABBA						Arrival						Lynn Ahrens	1
14	ABBA						The Visitors						AIR	2
15	Michael Abels						Us (Original Motion Picture Soundtrack)						Air Supply	1
16	AC/DC						Dirty Deeds Done Dirt Cheap						Akon	1
17	AC/DC						Back In Black						Mali Music	1
18	Across The Universe Cast						Across The Universe (Music From The Motion Picture)						Monkey	1
19	Louise Adams						Louise Adams
20	Adele						25
21	Aerosmith						Toys In The Attic
22	Aerosmith						I Don't Want To Miss A Thing
23	a-ha						Hunting High And Low
24	Lynn Ahrens	Stephen Flaherty	David Newman				Anastasia (Music From The Motion Picture)
25	AIR						Moon Safari
26	AIR						Le Voyage Dans La Lune
27	Air Supply						Love Songs
28	Akon	Eminem					Smack That
29	Mali Music						Mali Music
30	Monkey						Journey To The West
Sheet2

Cell Formulas
Range	Formula
P2:P23	P2	=IFERROR(INDEX($D$2:$D$30,MATCH(0,INDEX(COUNTIF($P$1:P1,$D$2:$D$30),),0)),"")
Q2:Q23	Q2	=IF(P2="","",COUNTIF($D$2:$D$30,P2)-MATCH(P2,$D$2:$D$30,0)*10^-10)
S2:S11	S2	=LOOKUP(2,1/($Q$2:$Q$23=LARGE($Q$2:$Q$23,ROWS($1:1))),$P$2:$P$23)
T2:T11	T2	=IF(S2="","",COUNTIF($D$2:$D$30,S2))

How do I do this to also include artists featured in columns E to I?

 

[bebo021999]

How do I do this to also include artists featured in columns E to I?

That fastest way I can offer is using column O to count then sort Largest to Smallest

Book1
	D	E	F	G	H	I	J	K	L	M	N	O
1	Artist	Additional Artist #1	Additional Artist #2	Additional Artist #3	Additional Artist #4	Additional Artist #5	Title					Count
2	10cc						The Very Best Of 10cc					1
3	The 12th Man						Wired World Of Sports					5
4	The 12th Man						12th Man Again!					5
5	The 12th Man						Bill Lawry...This Is Your Life					5
6	The 12th Man						The Final Dig?					5
7	The 12th Man						Boned!					5
8	2Pac	Dr. Dre					California Love					2
9	2Pac						Greatest Hits					2
10	ABBA						Ring Ring					5
11	ABBA						ABBA					5
12	ABBA						The Best Of ABBA					5
13	ABBA						Arrival					5
14	ABBA						The Visitors					5
15	Michael Abels						Us (Original Motion Picture Soundtrack)					1
16	AC/DC						Dirty Deeds Done Dirt Cheap					2
17	AC/DC						Back In Black					2
18	Across The Universe Cast						Across The Universe (Music From The Motion Picture)					1
19	Louise Adams						Louise Adams					1
20	Adele						25					1
21	Aerosmith						Toys In The Attic					2
22	Aerosmith						I Don't Want To Miss A Thing					2
23	a-ha						Hunting High And Low					1
24	Lynn Ahrens	Stephen Flaherty	David Newman				Anastasia (Music From The Motion Picture)					1
25	AIR						Moon Safari					2
26	AIR						Le Voyage Dans La Lune					2
27	Air Supply						Love Songs					1
28	Akon	Eminem					Smack That					1
29	Mali Music						Mali Music					1
30	Monkey						Journey To The West					1
Sheet2

Cell Formulas
Range	Formula
O2:O30	O2	=COUNTIF($D$2:$D$30,D2)