Cant stop it counting a blank cell as an option

[Beach Walker]

Hi

The below formula is looking through a big list of names, all name populate more than once, the formula pulls out a list of the names only once so I can see a shorter list of how many names a represented in the big list.

The problem is there is also blanks in this list of names and this formula is considering a Blank a name, the blank cell it counts does have a formula in it.

=IFERROR(INDEX(Reference!$G$2:$G$600,MATCH(0,COUNTIF(H$2:$H2,Reference!$G$2:$G$600)+IF(COUNTIF(Reference!$G$2:$G$600,Reference!$G$2:$G$600)>1,0,1),0)),"")

The part I have underlined I believe to be the problem but I cant work it out.

Assistance would be appreciated.

Its an Array formula.

Thanks

[sktneer]

You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter.

Assuming your Names are on the Reference Sheet in the range G2:G600 with blank cells in between and you want to extract the unique list of Names on another sheet starting from A2, then...

In A2

=IFERROR(INDEX(Reference!$G$2:$G$600,MATCH(0,INDEX(IF(Reference!$G$2:$G$600<>"",COUNTIF($A$1:A1,Reference!$G$2:$G$600)),0,0),0)),"")

Remember to change the column reference if you want to extract the list in a column other than col. A. So if you want to have this formula say in col. H starting from H2, the red range reference would be $H$1:H1 in the formula.

See if this helps.