Cant stop it counting a blank cell as an option

**Beach Walker** · 07-08-2014, 07:47 PM

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** · 07-08-2014, 08:21 PM

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

Please Login or Register  to view this content.

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.