VBA: if cell contains number, return value from other cell

[jcooooper]

Hi all,

Hopefully an easy one

In the below table, if a cell in named_range_2 has a number, I would like to return the corresponding value in named_range_1.

I am working with a datasets with 1000's of rows if that makes a difference

named_range_1	named_range_2
def	123
ghi
hjk
lmn	22
op
qrs
lmn	332
wxyz	96541

For example, the above table would return

def

lmn

wxyz

(unique list, so the double "lmn" is ignored).


Any help would be greatly appreciated.

Thanks!

 

[JEC]

Which Excel version do you use?

Office 365 makes it easy:

=UNIQUE(FILTER($A$2:$A$9,ISNUMBER($B$2:$B$9),""))

 

[jcooooper]

Microsoft 365 for enterprise. It says version 2008 which seems a bit dated?

 

[jcooooper]

Ok great, thanks.

One more question, I would like to compare this unique list with another column, and remove those appear in that new column.

For example:

unique list	named_range_3
def	lmn
lmn
wxyz

So in this case, the unique list would just be

def

wxyz

given lmn appears in named_range_3

Is that possible with formulas or do we need VBA?

Thanks,
John

 

[JEC]

You can refer to the output of the formula or do it within the formula. Assuming that named_range_3 is in column C

=FILTER(UNIQUE(FILTER($A$2:$A$9,ISNUMBER($B$2:$B$9),"")),COUNTIF($C$2:$C$9,UNIQUE(FILTER($A$2:$A$9,ISNUMBER($B$2:$B$9),"")))=0,"")

Or with LET if you have that function

=LET(x,UNIQUE(FILTER($A$2:$A$9,ISNUMBER($B$2:$B$9),"")),FILTER(x,COUNTIF($C$2:$C$9,x)=0,""))

 

[jcooooper]

that works perfect - thanks so much!

 

[JEC]

Nice, you're welcome!

 

[jcooooper]

Last request, promise.

If I want to add one additional condition, so in addition removing duplicates, it only returns where named_range_4 starts with"GB" or "SG", how would I add that?

 

[JEC]

Probably like this

=LET(x,UNIQUE(FILTER($A$2:$A$9,ISNUMBER($B$2:$B$9),"")),FILTER(x,(COUNTIF($C$2:$C$9,x)=0)*((LEFT(x,2)="GB")+(LEFT(x,2)="SG")),""))

 

[Peter_SSs]

Microsoft 365

Please put that in your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)