Create unique list from a data source that also contains blanks and #N/A

[scotthannaford1973]

Hi all

I have a range B2:B10 that contains a list of names but also includes a couple of #N/A and blanks (which are fine), but in the next column I want to do a list of unique items but which omits the #N/A and the blank.

Have started using {=INDEX(ResourceTypes,MATCH(0,COUNTIF($R$1:R8,ResourceTypes),""))} but cannot see how to ignore the blanks and the #N/A

 

[scotthannaford1973]

Sorry - not B2:B10, I meant R1:R8

 

[Weazel]

maybe something like...

=IFERROR(INDEX($R$2:$R$10,MATCH(0,IF($R$2:$R$10<>"",IF($R$2:$R$10<>"#N/A",COUNTIF($S$1:S1,$R$2:$R$10))),0)),"") Control Shift Enter

 

[eggyjla]

List of values named "ListA"
CSE in J2 copied down

{=IFERROR(LOOKUP(2, 1/((COUNTIF($J$1:J1, ListA)=0)*(ListA<>"")), ListA),"")}

 

[scotthannaford1973]

List of values named "ListA"
CSE in J2 copied down

{=IFERROR(LOOKUP(2, 1/((COUNTIF($J$1:J1, ListA)=0)*(ListA<>"")), ListA),"")}

thanks - will give that a try