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!

 

[jcooooper]

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’)

Done!

 

[jcooooper]

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")),""))

I tried this but couldn't get it to work. Do I need to substitute anything in apart from the column references?

For example, I pasted the bellow table in cell A2.

named_range_1	named_range_2	named_range_3
ABC	123​	SG123
DEF		HK456
GHI	456​
JKL	789​	GB789

Was hoping it would return:

ABC
JKL

but get the #name error

Do I need to substitute something in for "x"?

Many thanks!

 

[Fluff]

How about

=FILTER(A2:A10,(B2:B10<>"")*((LEFT(C2:C10,2)="GB")+(LEFT(C2:C10,2)="SG")))

 

[jcooooper]

How about

=FILTER(A2:A10,(B2:B10<>"")*((LEFT(C2:C10,2)="GB")+(LEFT(C2:C10,2)="SG")))

Works perfect on the smaller data set, but when I use in the real world with my named ranges (1000s of rows) I get the #CALC! error. Is that just the size of the calculation do you think?

 

[Fluff]

That error suggests that there are no rows which meat the criteria.

 

[Peter_SSs]

Done!

Thanks for updating your profile.

You have mentioned named ranges in all your questions. If you have those, it makes sense to me to use them in your formulas as it then does not matter what columns they are in or what rows they occupy.

From the descriptions it seems that named_range_1, named_range_2 and named_range_4 must all contain the same number of rows since you are trying to compare what is on the same row in each of those ranges.
named_range_3 I am not so sure about?

I am not sure I have grasped the exact details of your requirements, but could you use some thing like this?

jcooooper.xlsm
	A	B	C	D	E	F	G	H
1	named_range_1	named_range_2	named_range_3	named_range_4		Post 1 Qn	Post 4 Qn	Post 8 Qn
2	def	123	lmn			def	def	ABC
3	ghi		wxyz			lmn	ABC	JKL
4	def	456				wxyz	PQR
5	hjk					ABC	JKL
6	lmn	22		SG33		PQR
7	op					JKL
8	qrs
9	lmn	332
10	wxyz	96541
11	ABC	123		SG123
12	XYZ			HK456
13	PQR	456
14	JKL	789		GB789
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=UNIQUE(FILTER(named_range_1,ISNUMBER(named_range_2),""))
G2:G5	G2	=LET(f,UNIQUE(FILTER(named_range_1,ISNUMBER(named_range_2),"")),FILTER(f,ISNA(MATCH(f,named_range_3,0)),""))
H2:H3	H2	=LET(f,FILTER(named_range_1,ISNUMBER(named_range_2)*ISNUMBER(FIND(LEFT(named_range_4&".",2),"|GB|SG|")),""),UNIQUE(FILTER(f,ISNA(MATCH(f,named_range_3,0)),"")))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
named_range_1	=Sheet1!$A$2:$A$14	F2:H2
named_range_2	=Sheet1!$B$2:$B$14	F2:H2
named_range_3	=Sheet1!$C$2:$C$3	G2:H2
named_range_4	=Sheet1!$D$2:$D$14	H2