Unique values in column but omit zeros and blanks

0
votes

In column B I've got the list of values of which some occur multiple times. What I am trying to achieve is to list unique values of such list in column E. For cell E3 I use the array formula like this:

{=IFERROR(INDEX($B$3:$B$20,MATCH(0,COUNTIF($E$2:E2, $B$3:$B$20), 1)),"")}

but I'm not sure how to add another layer to this formula which would drop all blank cells from B. Now the formula treat blanks as the zero value and returns zero in the first row.

1
excelexcel-formula
Are there zero's in your list too, or do you only need to omit blanks? – ashleedawg
There only blanks in column B. But the alternative formula to exclude zero or any other chosen value in the result would be also neat :) – friedman

1 Answers

2
votes

Paste this into D3 and copy down to the cells below to get a unique list of non-blanks value that are in B3:B20:

=LOOKUP(2, 1/((COUNTIF($D$2:D2, $B$3:$B$20)=0)*($B$3:$B$20<>"")), $B$3:$B$20)

You can just enter this normally since it is not an array formula.

It you copy the formula into more cells than there are unique values, you're get #N/A errors. You can avoid this by using IFERROR:

=IFERROR(LOOKUP(2,1/((COUNTIF($D$2:D2,$B$3:$B$20)=0)*($B$3:$B$20<>"")),$B$3:$B$20),"")

(Source)