Extract unique list from column a if column x has a value

[Fazila]

As the title says really. I have a table which looks something like:

	Ethnicity	English	Maths	Science	Triple Science	Art	Drama	Citizenship	Geography
1	Black Caribbean	Y	Y	Y			Y		Y
2	Afghan	Y	Y	Y		Y		Y
3	Afghan	Y	Y	Y			Y		Y
4	Indian	Y	Y	Y	Y	Y		Y	Y

<tbody>
</tbody>

So I want a unique list of Ethnicities for all students who are studying Art.

At the moment I am using formula {=IFERROR(INDEX(Ethnicity,MATCH(0,COUNTIF($B$30:B30,Ethnicity),0)),"")}, however, this just gives me a list of unique ethnicities for the entire cohort of students not just the ones studying the subject.

Any help would be appreciated.

Thanks

 

[Weazel]

Maybe something like...

	A	B	C	D	E	F	G	H	I
1	Ethnicity	English	Maths	Science	Triple Science	Art	Drama	Citizenship	Geography
2	Black Caribbean	Y	Y	Y			Y		Y
3	Afghan	Y	Y	Y		Y		Y
4	Afghan	Y	Y	Y			Y		Y
5	Indian	Y	Y	Y	Y	Y		Y	Y
6
7
8	Drama
9	Black Caribbean
10	Afghan

<tbody>
</tbody>

A9=IFERROR(INDEX($A$2:$A$5,SMALL(IF(FREQUENCY(IF($B$2:$I$5="Y",IF($B$1:$I$1=$A$8,MATCH($A$2:$A$5,$A$2:$A$5,0))),ROW($A$2:$A$5)-ROW($A$2)+1),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$9:A9))),"") Control Shift Enter

 

[Fazila]

Maybe something like...

	A	B	C	D	E	F	G	H	I
1	Ethnicity	English	Maths	Science	Triple Science	Art	Drama	Citizenship	Geography
2	Black Caribbean	Y	Y	Y			Y		Y
3	Afghan	Y	Y	Y		Y		Y
4	Afghan	Y	Y	Y			Y		Y
5	Indian	Y	Y	Y	Y	Y		Y	Y
6
7
8	Drama
9	Black Caribbean
10	Afghan

<tbody>
</tbody>

A9=IFERROR(INDEX($A$2:$A$5,SMALL(IF(FREQUENCY(IF($B$2:$I$5="Y",IF($B$1:$I$1=$A$8,MATCH($A$2:$A$5,$A$2:$A$5,0))),ROW($A$2:$A$5)-ROW($A$2)+1),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$9:A9))),"") Control Shift Enter

Thanks for posting Weazel. The data posted above is only a very small sample so I think the formula would get very convoluted if I was to delete rows with every ethnicity.

 

[Weazel]

i don't know what you mean by deleting rows....