Matching a partial string

[steedy]

Hi,

First time here, I hope somebody can help me. I have 2 Columns, in Column A I have a string that is 12 characters in length (fixed), in column B I could have exactly the same string, I need to make sure that A2 string = B2, A3 =B3 etc..... if I use the exact function this replies with a true or false answer which is great. However what i need is a true or false answer if 9 or more of the characters match, in the example below the 1st and last strings obviousley fail with the Exact function however I need them to pass as >9 match.

Any examples are greatfully received.

F0FXE98MUBQF	J0FXE98MJBQF	FALSE
1ZP0XDJYKG5K	1ZP0XDJYKG5K	TRUE
GV7LRZKJGCY3	GV7LRZKJGCY3	TRUE
XBLD06NNNTDS	XBLD06NNNTDS	TRUE
G9LF2Y4KP49B	G9LF2Y4KP49B	TRUE
VGLE5JYKXFVM	VGLE5JYKXFVM	TRUE
291SN1Y8V5AN	291SN1Y8V5AN	TRUE
CY97EBEE46TZ	CY97EBEE46TZ	TRUE
NFXH3KSQC4UE	NFXH3KSQC4UE	TRUE
S0VM75RDELAR	S0VM75RDELAR	TRUE
QZL4H7GNH94A	QZL4H7GNH94A	TRUE
CMRN1YSJQT2Z	CMRN1YSJQT2Z	TRUE
FTHAAPMLN8JF	THAAPMLN8JF	FALSE

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

 

[XOR LX]

Hi.

Just to clarify: you don't mean that 9 or more characters have to match and also occupy the same position within the string, or even the same relative position to each other? If this was the case, your last example would not pass the test.

It would also mean that:

ABCDEFGHIJ

and

IEADFBGCJX

would be considered a match.

Can you just confirm this?

Regards

 

[V_Malkoti]

Looking at examples he gave, I think that's exactly what he means.

 

[XOR LX]

Looking at examples he gave, I think that's exactly what he means.

Perhaps, but then they wouldn't be much of a match, would they?

And if my assumption was false, then we might be getting into the murky area of "fuzzy matching", unless of course the OP can come up with a more rigorous definition of what constitutes 9 or more characters "matching" for a given pair of strings.

Regards

 

[steedy]

Thanks for the replies so far, the characters would not be mixed up as per the
ABCDEFGHIJ

and

IEADFBGCJX


so relative position would be the same unless the 1st character was missed and then the whole string would shift by 1 place, in this instance if it is to complicated it doesnt matter, so lets say the string is always 12 characters long and 9 out of the 12 must match and have the same relative position. Is there a way to do this ??

 

[XOR LX]

So can you give some more varied examples together with your expected results, just so it's clear?

Regards

 

[steedy]

Hi, so I've looked at this a bit more and think for ease we should say both strings should contain 12 characters, if 9 out of the 12 have the same relative position then it gives a true response. Here are some example codes.

Using the Exact function this has highlighted 3 strings that give a False but actually should return a True

F0FXE98MUBQF	J0FXE98MJBQF	FALSE
1ZP0XDJYKG5K	1ZP0XDJYKG5K	TRUE
GV7LRZKJGCY3	GV7LRZKJGCY3	TRUE
VGLE5JYKXFVM	VGLE5JYKXFVM	TRUE
291SN1Y8V5AN	291SN1Y8V5AN	TRUE
CY97EBEE46TZ	CY97EBEE46TZ	TRUE
NFXH3KSQC4UE	NFXH3KSQC4UE	TRUE
S0VM75RDELAR	S0VM75RDELAR	TRUE
QZL4H7GNH94A	QZL4H7GNH94A	TRUE
CMRN1YSJQT2Z	CMRN1YSJQT2Z	TRUE
V9S9MFKAW02D	V9S9MFKAW02D	TRUE
KCEARLJJ6J21	KCEARLJJ6J21	TRUE
L48ZT6B5DB79	L48ZT6B5DB79	TRUE
KYBERTFDGD4X	KYBERTFDGD4X	TRUE
B1C6LXY2Z6S8	B1C6LXY2Z6S8	TRUE
0VNN86R29C8J	0VNN86R29C8J	TRUE
A7BMKT8YPN0Z	A7BMKT8YPN0Z	TRUE
1NW3FWG9CWKE	1JW3FWG9CWKE	FALSE
5C5DKA1CJP5E	5C5DKA1CJP5E	TRUE
A9CDNG73TPWC	A9CDNG73TPWC	TRUE
39X3R6KB82AT	39X3R6KB82AT	TRUE
35L9H4SLZYSD	35L9H4SLZYSD	TRUE
N5W4D9WXJKPB	J5W4D9WXJKPB	FALSE

<colgroup><col span="3"></colgroup><tbody>
</tbody>

More FALSE examples, the one highlighted in RED really is false

F7M61Q0J5GEV	J7M61Q0J5GEV	FALSE
F4Q1UX21YFG9	J4Q1UX21YFG9	FALSE
R4XPNT40TDAS	R4XPJJ14QTDA	FALSE
MEEM0MMEXYX9	JEEM0MMEXYX9	FALSE
HC6KPTZ27K8V	JC6KPTZ27K8V	FALSE
E8ZLG3GRRHWC	J8ZLG3GRRHWC	FALSE
Z8TEBCKMMU44	J8TEBCKMMU44	FALSE
R5YFTW2RWZEC	P5YFTW2RWZEC	FALSE
E0UY5TC4HDFL	J0UY5TC4HDFL	FALSE
GZNB6YYK6N0R	ZJJB6YYK6N0R	FALSE
H8FS8PJJUKVH	J8FS8PJJUKVH	FALSE
45ESU9SKTZ3F	45ESJ9SKTZ3F	FALSE
HL96YNXFG8L2	JL96YNXFG8L2	FALSE
7U28Y2KYCPUY	4U28Y2KYCPUY	FALSE
DULDANPCW4B6	DJLDANPCW4B6	FALSE
U6DH0ADP2622	J6DH0ADP2622	FALSE
R83PNV3D9EQ5	P83PNV3D9EQ5	FALSE
H6MXVN638PWR	J6MXVN638PWR	FALSE
XBWKJY9XRE7M	KBWKJY9XRE7M	FALSE
T5J4XX317F4D	J5J4XX317F4D	FALSE
KU4EFDAM0HF3	KD4EFDAM0HF3	FALSE
UXENVWQ0X0Z3	JXENVWQ0X0Z3	FALSE
VCZJNXNTTXN5	WCZJNXNTTXN5	FALSE
H45EE87DNBXD	J45EE87DNBXD	FALSE
N2GVRMDSSAZ2	J2GVRMDSSAZ2	FALSE

<colgroup><col span="3"></colgroup><tbody>
</tbody>

A8KPX8Y1NMVV

A8KPX8K1TNMV

FALSE

<tbody>
</tbody>

 

[Rick Rothstein]

..if 9 out of the 12 have the same relative position then it gives a true response.

I must be reading something wrong here, but based on what you say above, I think every one of your examples are TRUE. Here is the array-entered** formula I used to get those results...

=SUM(0+(MID(A1,ROW(1:12),1)=MID(B1,ROW(1:12),1)))>=9

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[steedy]

Hi Rick, Thank I will try this, the only one that is wrong is the one highlighted in RED

 

[steedy]

Hi Rick, I must be doing something stupid this gives "me" a False for everyone.....