SOLVED

Find keys with no corresponding empty rows

Copper Contributor

Hi! 

 

I want find all keys where none of the corresponding values are empty. So in the example below I want "Name two" and "Name three" as a result. Shouldn't be hard but I just can't come up with a way!?  

 

Key

Value

Name one 

123
Name one 345
Name one 456
Name one  
Name one 345
Name two345
Name two435
Name two576
Name two678
Name two898
Name three 345
Name three 345
Name three 456
Name three 324
Name four  
Name four 234
Name four  
Name four  
Name four 876
  
  
Wanted result (no empty cells in row B) 
Name two  
Name three 
2 Replies
best response confirmed by Torbjorn_Hedberg (Copper Contributor)
Solution

@Torbjorn_Hedberg 

If you have Microsoft 365 or Office 2021:

 

=UNIQUE(FILTER(A2:A20,COUNTIFS(A2:A20,A2:A20,B2:B20,"")=0))

Amazing. Thanks so much!
1 best response

Accepted Solutions
best response confirmed by Torbjorn_Hedberg (Copper Contributor)
Solution

@Torbjorn_Hedberg 

If you have Microsoft 365 or Office 2021:

 

=UNIQUE(FILTER(A2:A20,COUNTIFS(A2:A20,A2:A20,B2:B20,"")=0))

View solution in original post