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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Ethnicity[/TD]
[TD]English[/TD]
[TD]Maths[/TD]
[TD]Science[/TD]
[TD]Triple Science[/TD]
[TD]Art[/TD]
[TD]Drama[/TD]
[TD]Citizenship[/TD]
[TD]Geography[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Black Caribbean[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Afghan[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Afghan[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Indian[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]

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...


[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ethnicity[/TD]
[TD]English[/TD]
[TD]Maths[/TD]
[TD]Science[/TD]
[TD]Triple Science[/TD]
[TD]Art[/TD]
[TD]Drama[/TD]
[TD]Citizenship[/TD]
[TD]Geography[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Black Caribbean[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Afghan[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Afghan[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Indian[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Drama[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Black Caribbean[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Afghan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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...


[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ethnicity[/TD]
[TD]English[/TD]
[TD]Maths[/TD]
[TD]Science[/TD]
[TD]Triple Science[/TD]
[TD]Art[/TD]
[TD]Drama[/TD]
[TD]Citizenship[/TD]
[TD]Geography[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Black Caribbean[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Afghan[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Afghan[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Indian[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Drama[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Black Caribbean[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Afghan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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....