Sorry, I'm still a beginner with Excel. Could you please provide show me an example specific to my attached workbook?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
What you have attached is a screenshot, can't work on that.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.
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 |
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?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))
That fastest way I can offer is using column O to count then sort Largest to SmallestHow do I do this to also include artists featured in columns E to I?
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) |