I have a list of song titles in Col A, and several column headers of 'categories' or 'themes' in Row 1 (ie. 'Christmas', 'Advent', etc). For each song, I put an 'x' under each column/theme for every theme that is found in the song (ie. 'Christmas, Advent, etc).
Data Table:
Theme1 Theme2 Theme3
Song1 x x
Song2 x
Song3 x x
Desired results on separate worksheet:
Theme1 Theme2 Theme3
Song1 Song2 Song1
Song3 Song3
The closest I can get is a list of duplicates like this using an INDEX-MATCH combination.
Song1
Song2
Song2
Song2
Song3
Song3
Song4
I have a dynamic named range for the Song Titles as follows:
'Titles'='Theme Data'!$A$2:INDEX('Theme Data'!$A$1:A1005,MATCH(REPT("Z",5),'Theme Data'!$A$1:A1005))
Thanks to excelisfun.
My formula skills are few so I know that there has a to be a way to remove the duplicates but I'm not sure how. And I need the formulas for each theme column to be dynamic dependent on the song titles. I'd prefer NOT to reformat my table but I can if I have to.
Any help would be greatly appreciated!
Data Table:
Theme1 Theme2 Theme3
Song1 x x
Song2 x
Song3 x x
Desired results on separate worksheet:
Theme1 Theme2 Theme3
Song1 Song2 Song1
Song3 Song3
The closest I can get is a list of duplicates like this using an INDEX-MATCH combination.
Song1
Song2
Song2
Song2
Song3
Song3
Song4
I have a dynamic named range for the Song Titles as follows:
'Titles'='Theme Data'!$A$2:INDEX('Theme Data'!$A$1:A1005,MATCH(REPT("Z",5),'Theme Data'!$A$1:A1005))
Thanks to excelisfun.
My formula skills are few so I know that there has a to be a way to remove the duplicates but I'm not sure how. And I need the formulas for each theme column to be dynamic dependent on the song titles. I'd prefer NOT to reformat my table but I can if I have to.
Any help would be greatly appreciated!