Identifying close (not exact) matches from two columns

[fauxlidae]

I'm trying to create something that will identify a a 10 character sequence within a group of other characters in the same cell, compare it with a set of similar data in an adjacent column, and identify whether that same 10 character sequence appears anywhere in the second column.

The cells that may contain matching references are not typically in adjacent cells, only adjacent columns, and the data can be thousands of rows long.

The characters that must be matched will always be 10 adjacent characters, but the text they are within is not always the same length, not is the positioning within it the same.

Any help much appreciated!

 

[Muhammad_Usman]

1235.xlsm
	A	B	C
1	Data1	Data2	Result
2	12345	12345	TRUE
3	222	222	FALSE
4	AAABBBBBC	BBBBBQQQQQQ	TRUE
5	2565635789642.00	AAA56357DFGH	TRUE
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A2, ROW(INDIRECT("1:" & LEN(A2)-4)), 5), B2))))>0, TRUE, FALSE)

You Just Change 5 to 10 in formula or change number according to your criteria

 

[fauxlidae]

1235.xlsm
	A	B	C
1	Data1	Data2	Result
2	12345	12345	TRUE
3	222	222	FALSE
4	AAABBBBBC	BBBBBQQQQQQ	TRUE
5	2565635789642.00	AAA56357DFGH	TRUE
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A2, ROW(INDIRECT("1:" & LEN(A2)-4)), 5), B2))))>0, TRUE, FALSE)

You Just Change 5 to 10 in formula or change number according to your criteria

Thank you!!

It looks like the info needs to be in the adjacent cell at the moment - do you know of a way for it to scan the entire column for a match to a cell?

Tried adapting to =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A:A, ROW(INDIRECT("1:" & LEN(A2)-4)), 5), B:B))))>0, TRUE, FALSE) with no luck.

Many thanks!

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this does what you want.

fauxlidae.xlsm
	A	B	C
1
2	zaed1234567898ref	ijuhygtf1234567898tref	TRUE
3	lkjuhsnttttt56723kflsd	refsnttttt56723te	TRUE
4	restluckyshot546	156478uytgfrt	TRUE
5	abcdefghijklmno	turmoilluckyshot57689564	FALSE
6	iuiwhejwqektree52134stone	tree52134s472	TRUE
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=COUNT(MATCH("*"&MID(A2,SEQUENCE(LEN(A2)-9),10)&"*",B:B,0))>0

 

[fauxlidae]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this does what you want.

fauxlidae.xlsm
	A	B	C
1
2	zaed1234567898ref	ijuhygtf1234567898tref	TRUE
3	lkjuhsnttttt56723kflsd	refsnttttt56723te	TRUE
4	restluckyshot546	156478uytgfrt	TRUE
5	abcdefghijklmno	turmoilluckyshot57689564	FALSE
6	iuiwhejwqektree52134stone	tree52134s472	TRUE
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=COUNT(MATCH("*"&MID(A2,SEQUENCE(LEN(A2)-9),10)&"*",B:B,0))>0

Oh that generator is so muhc easier lol, thank you!

Getting a #NAME? error from this formula sadly - any ideas?

 

[Peter_SSs]

Getting a #NAME? error from this formula sadly - any ideas?

Your profile shows that you are using MS365.

Is that still correct? #NAME? error indicates that you may be using an older version?