Filter column based on partial match from another column

[chive90]

Sheet1 Column A I have thousands of ID numbers

Sheet2 Column A these ID numbers exist as part of a much larger string

I require Sheet2 Column A to filter where any of these ID numbers have been found, regardless of where they are in the string.

Tried using advanced filter but I believe it requires an exact match.

Is there another filtering solution or formula I can use to achieve this?

Thank you

 

[chive90]

Or, if filtering is not possible, a search which then returns a yes/no if found?

e.g. in Sheet2 Column B can I enter a formula which searches whether A2 contains any of the ID numbers captured in Sheet1 A:A in any part of its string? If it does return Yes, if it doesn't return No? Example data:

Sheet1 Column A:

20578950

45045645

54087780

54064135

98978078

Sheet2 Columns A and B:

shjdshdj/ksjdksa/54064135/djashjdk/ksjljk.com	Yes
jkhsakjdhslakd/kjksajdk/djksjdksa.com	No
ioqiuieur/dskjdka/dskjdkas/98978078/dksajdksa/jdkajsdklsa.com	Yes
wewjhejwhe/dskdjsak.com	No
dsdsadsadsjrkj/45045645/popowqeqw	Yes

This would also work if filtering is not possible. Thanks

 

[Alex Blakenburg]

Someone else might be able to help you with a formula since you don't have access to MS 365.
If you want to pursue the Advanced filter you just need to use a helper column.
The helper column must have the same column heading as the heading for the equivalent column in the data being filtered.

Sheet 1 - Criteria

Book3
	A	B
1	ID	Text
2	20578950	*20578950*
3	45045645	*45045645*
4	54087780	*54087780*
5	54064135	*54064135*
6	98978078	*98978078*
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	="*"&A2&"*"

Sheet 2 - data to be filtered

Book3
	A	B
1	Text	YN
2	shjdshdj/ksjdksa/54064135/djashjdk/ksjljk.com	Yes
3	jkhsakjdhslakd/kjksajdk/djksjdksa.com	No
4	ioqiuieur/dskjdka/dskjdkas/98978078/dksajdksa/jdkajsdklsa.com	Yes
5	wewjhejwhe/dskdjsak.com	No
6	dsdsadsadsjrkj/45045645/popowqeqw	Yes
Sheet2

Filter set up includes heading row:

 

[Sergius]

As an option

16605_Выделение_числа_из_текста.xls
	A	B
1	shjdshdj/ksjdksa/54064135/djashjdk/ksjljk.com	Yes
2	jkhsakjdhslakd/kjksajdk/djksjdksa.com	No
3	ioqiuieur/dskjdka/dskjdkas/98978078/dksajdksa/jdkajsdklsa.com	Yes
4	wewjhejwhe/dskdjsak.com	No
5	dsdsadsadsjrkj/45045645/popowqeqw	Yes
Sheet2

Cell Formulas
Range	Formula
B1:B5	B1	=IF(COUNT(FIND(Sheet1!$A$1:$A$5,$A1))>0,"Yes","No")

 

[chive90]

Someone else might be able to help you with a formula since you don't have access to MS 365.
If you want to pursue the Advanced filter you just need to use a helper column.
The helper column must have the same column heading as the heading for the equivalent column in the data being filtered.

Sheet 1 - Criteria

Book3
	A	B
1	ID	Text
2	20578950	*20578950*
3	45045645	*45045645*
4	54087780	*54087780*
5	54064135	*54064135*
6	98978078	*98978078*
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	="*"&A2&"*"

Sheet 2 - data to be filtered

Book3
	A	B
1	Text	YN
2	shjdshdj/ksjdksa/54064135/djashjdk/ksjljk.com	Yes
3	jkhsakjdhslakd/kjksajdk/djksjdksa.com	No
4	ioqiuieur/dskjdka/dskjdkas/98978078/dksajdksa/jdkajsdklsa.com	Yes
5	wewjhejwhe/dskdjsak.com	No
6	dsdsadsadsjrkj/45045645/popowqeqw	Yes
Sheet2

Filter set up includes heading row:
View attachment 116679

Thank you. I did try this earlier without the * either side and it did not work.
Is the * either side just some sort of wildcard which essentially says look for the number regardless of any text either side of it?

With the * either side of the number, it appears to be filtering correctly - thank you very much!

It would be great however if I could validate the accuracy of the filter with a formula for example using the below method however this does not appear to be working.

As an option

16605_Выделение_числа_из_текста.xls
	A	B
1	shjdshdj/ksjdksa/54064135/djashjdk/ksjljk.com	Yes
2	jkhsakjdhslakd/kjksajdk/djksjdksa.com	No
3	ioqiuieur/dskjdka/dskjdkas/98978078/dksajdksa/jdkajsdklsa.com	Yes
4	wewjhejwhe/dskdjsak.com	No
5	dsdsadsadsjrkj/45045645/popowqeqw	Yes
Sheet2

Cell Formulas
Range	Formula
B1:B5	B1	=IF(COUNT(FIND(Sheet1!$A$1:$A$5,$A1))>0,"Yes","No")

Thanks for posting. I have tried this but it does not appear to work.

I tested over 10 rows in Sheet2, with row 8 being the only one that contains a number located within the range of Column A Sheet1, but it still returns "No".

The number is captured mid string, e.g. sdjsajdklsad/dsjkjdakas/sjkadjklsd/20000130/djkldjlksa/djkdlajdks/

This number is also captured on row 12 of Sheet1, column A.

 

[Sergius]

Did you substitute your range or the one I gave? The formula I gave looks for numbers regardless of where they are.
Can you show the formula for line 12 where there are numbers?

 

[chive90]

Did you substitute your range or the one I gave? The formula I gave looks for numbers regardless of where they are.
Can you show the formula for line 12 where there are numbers?

I substituted in my range.

C8 of Sheet2 = ioqiuieur/dskjdka/dskjdkas/98978078/dksajdksa/jdkajsdklsa/
D8 of Sheet2 = =IF(COUNT(FIND(Sheet1!$A$2:$A$2000,$C8))>0,"Yes","No")
A12 of Sheet1 = 98978078

Value returned in D8 of Sheet2 = No but should be Yes.

There are no formulas in Sheet1 A:A - just numbers.

 

[Sergius]

And this is what it shows me

Book1.xlsm
	A
1	20578950
2	45045645
3	54087780
4	54064135
5	98978078
6	5879517
Sheet1

Book1.xlsm
	A	B
1	shjdshdj/ksjdksa/54064135/djashjdk/ksjljk.com	Yes
2	jkhsakjdhslakd/kjksajdk/djksjdksa.com	No
3	ioqiuieur/dskjdka/dskjdkas/98978078/dksajdksa/jdkajsdklsa.com	Yes
4	wewjhejwhe/dskdjsak.com	No
5	dsdsadsadsjrkj/45045645/popowqeqw	Yes
6	ioqiuieur/dskjdka/dskjdkas/5879517/dksajdksa/jdkajsdklsa/	Yes
7	wewjhejwhe/dskdjsak.com	No
8	ioqiuieur/dskjdka/dskjdkas/98978078/dksajdksa/jdkajsdklsa/	Yes
Sheet2

Cell Formulas
Range	Formula
B1:B8	B1	=IF(COUNT(FIND(Sheet1!$A$1:$A$6,$A1))>0,"Yes","No")

 

[chive90]

Hmm that is strange. I dragged the formula down across all data (hundreds of thousands of rows) and is says No for every single one

This function works ok with Office 2016, right?