Unique Filter

[rhombus4]

Hi I want to filter column A:B where A Isn't Blank AND column D is Blank

I have
=Unique(filter(a:b,(a:a<>"")*(d:d="")))

But same values in column A appear more than once with formula above

I.e. dog appears in A2 a8 and a9 and d8 and d9 are blank but In formula dog appears twice.

Seems unique is based on column a and b. I only want it unique on column a

 

[rhombus4]

Ok

Hi
Hope this makes more sense
Data is in A2:D10 see bottom. PS Values in Column C are irrelevant and not needed

Original formula was =Unique(filter(a:b,(a:a<>"")*(d:d=""))) - which shows All Values in Col A:B where D is Blank)

Which gives me

cat	2​
elephant	3​
ant	4​
fox	5​
dog	1​

However I don't want to show dog or fox because one of the rows with dog(D2) and one of the row with fox(D10) has a value

I only want to Show unique values where every occurrence of value in Column A has blank in Column D
So end result should be below as every occurrence of cat elephant and ant have a blank entry in Column D

cat	2​
elephant	3​
ant	4​

	A	B	C	D
2​	dog	1​	192​	qwrqwer
3​	cat	2​	8​
4​	cat	2​	145​
5​	elephant	3​	112​
6​	ant	4​	125​
7​	fox	5​	161​
8​	dog	1​	48​
9​	dog	1​	16​
10​	fox	5​	30​	qwerw

 

[Fluff]

Ok, how about

=UNIQUE(FILTER(A2:B100,(COUNTIFS(D2:D100,"?*",A2:A100,A2:A100)=0)*(A2:A100<>"")))

 

[rhombus4]

Ok, how about

=UNIQUE(FILTER(A2:B100,(COUNTIFS(D2:D100,"?*",A2:A100,A2:A100)=0)*(A2:A100<>"")))

Thanks that works for the Test Data I have. Although really slow with real Data 50000 rows

 

[Fluff]

Don't know if this will be faster.

=LET(f,UNIQUE(FILTER(A2:A50000,D2:D50000<>"")),UNIQUE(FILTER(A2:B50000,(A2:A50000<>"")*(ISNA(MATCH(A2:A50000,f,0))))))

 

[rhombus4]

=LET(f,UNIQUE(FILTER(A2:A50000,D2:D50000<>"")),UNIQUE(FILTER(A2:B50000,(A2:A50000<>"")*(ISNA(MATCH(A2:A50000,f,0))))))

That seem alot quicker thanks. Is it possible without the let argument or would that make it lot slower
I work on 2 machines and only 1 allows the let function

 

[Fluff]

You can use it without the LET like

=UNIQUE(FILTER(A2:B50000,(A2:A50000<>"")*(ISNA(MATCH(A2:A50000,UNIQUE(FILTER(A2:A50000,D2:D50000<>"")),0)))))

 

[rhombus4]

=UNIQUE(FILTER(A2:B50000,(A2:A50000<>"")*(ISNA(MATCH(A2:A50000,UNIQUE(FILTER(A2:A50000,D2:D50000<>"")),0)))))

Thanks for all your Help Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.