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.

[TABLE="width: 322"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]F0FXE98MUBQF
[/TD]
[TD]J0FXE98MJBQF[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]1ZP0XDJYKG5K[/TD]
[TD]1ZP0XDJYKG5K[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]GV7LRZKJGCY3[/TD]
[TD]GV7LRZKJGCY3[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]XBLD06NNNTDS[/TD]
[TD]XBLD06NNNTDS[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]G9LF2Y4KP49B[/TD]
[TD]G9LF2Y4KP49B[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]VGLE5JYKXFVM[/TD]
[TD]VGLE5JYKXFVM[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]291SN1Y8V5AN[/TD]
[TD]291SN1Y8V5AN[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]CY97EBEE46TZ[/TD]
[TD]CY97EBEE46TZ[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]NFXH3KSQC4UE[/TD]
[TD]NFXH3KSQC4UE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]S0VM75RDELAR[/TD]
[TD]S0VM75RDELAR[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]QZL4H7GNH94A[/TD]
[TD]QZL4H7GNH94A[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]CMRN1YSJQT2Z[/TD]
[TD]CMRN1YSJQT2Z[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]FTHAAPMLN8JF
[/TD]
[TD]THAAPMLN8JF[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 258"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[strooman]

Perhaps I'm totally missing the point here. Excuse me for that.
That being said, if 9 (or more) characters out of 12 characters match, the outcome should be TRUE. Is that correct? Or should ALL characters match?
What you mean by "Relative Position"? That the character should appear in the string no matter what position?

I created a another layout just to compare more easy. Would you please comment on this example and what should be your outcome?

* NOM = Number of characters that match.
[TABLE="class: grid, width: 710"]
<tbody>[TR]
[TD]CODE[/TD]
[TD]According to you[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]* NOM[/TD]
[TD]According to me[/TD]
[/TR]
[TR]
[TD]F0FXE98MUBQF
[/TD]
[TD]FALSE[/TD]
[TD]F[/TD]
[TD]0[/TD]
[TD]F[/TD]
[TD]X[/TD]
[TD]E[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]M[/TD]
[TD]U[/TD]
[TD]B[/TD]
[TD]Q[/TD]
[TD]F[/TD]
[TD]
[/TD]
[TD]10[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]J0FXE98MJBQF[/TD]
[TD]FALSE[/TD]
[TD]J[/TD]
[TD]0[/TD]
[TD]F[/TD]
[TD]X[/TD]
[TD]E[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]M[/TD]
[TD]J[/TD]
[TD]B[/TD]
[TD]Q[/TD]
[TD]F[/TD]
[TD]
[/TD]
[TD]10[/TD]
[TD]TRUE
[/TD]
[/TR]
[TR]
[TD]1ZP0XDJYKG5K
[/TD]
[TD]TRUE[/TD]
[TD]1[/TD]
[TD]Z[/TD]
[TD]P[/TD]
[TD]0[/TD]
[TD]X[/TD]
[TD]D[/TD]
[TD]J[/TD]
[TD]Y[/TD]
[TD]K[/TD]
[TD]G[/TD]
[TD]5[/TD]
[TD]K[/TD]
[TD]
[/TD]
[TD]12[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1ZP0XDJYKG5K[/TD]
[TD]TRUE[/TD]
[TD]1[/TD]
[TD]Z[/TD]
[TD]P[/TD]
[TD]0[/TD]
[TD]X[/TD]
[TD]D[/TD]
[TD]J[/TD]
[TD]Y[/TD]
[TD]K[/TD]
[TD]G[/TD]
[TD]5[/TD]
[TD]K[/TD]
[TD]
[/TD]
[TD]12[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1NW3FWG9CWKE
[/TD]
[TD]FALSE[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]W[/TD]
[TD]3[/TD]
[TD]F[/TD]
[TD]W[/TD]
[TD]G[/TD]
[TD]9[/TD]
[TD]C[/TD]
[TD]W[/TD]
[TD]K[/TD]
[TD]E[/TD]
[TD]
[/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1JW3FWG9CWKE[/TD]
[TD]FALSE[/TD]
[TD]1[/TD]
[TD]J[/TD]
[TD]W[/TD]
[TD]3[/TD]
[TD]F[/TD]
[TD]W[/TD]
[TD]G[/TD]
[TD]9[/TD]
[TD]C[/TD]
[TD]W[/TD]
[TD]K[/TD]
[TD]E[/TD]
[TD]
[/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]N5W4D9WXJKPB
[/TD]
[TD]FALSE[/TD]
[TD]N[/TD]
[TD]5[/TD]
[TD]W[/TD]
[TD]4[/TD]
[TD]D[/TD]
[TD]9[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]P[/TD]
[TD]B[/TD]
[TD]
[/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]J5W4D9WXJKPB[/TD]
[TD]FALSE[/TD]
[TD]J[/TD]
[TD]5[/TD]
[TD]W[/TD]
[TD]4[/TD]
[TD]D[/TD]
[TD]9[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]P[/TD]
[TD]B[/TD]
[TD]
[/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]291SN1Y8V5AN
[/TD]
[TD]TRUE[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]S[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]8[/TD]
[TD]V[/TD]
[TD]5[/TD]
[TD]A[/TD]
[TD]N[/TD]
[TD]
[/TD]
[TD]12[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]291SN1Y8V5AN[/TD]
[TD]TRUE[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]S[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]8[/TD]
[TD]V[/TD]
[TD]5[/TD]
[TD]A[/TD]
[TD]N[/TD]
[TD]
[/TD]
[TD]12[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]F7M61Q0J5GEV
[/TD]
[TD]FALSE[/TD]
[TD]F[/TD]
[TD]7[/TD]
[TD]M[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]Q[/TD]
[TD]0[/TD]
[TD]J[/TD]
[TD]5[/TD]
[TD]G[/TD]
[TD]E[/TD]
[TD]V[/TD]
[TD]
[/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]J7M61Q0J5GEV[/TD]
[TD]FALSE[/TD]
[TD]J[/TD]
[TD]7[/TD]
[TD]M[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]Q[/TD]
[TD]0[/TD]
[TD]J[/TD]
[TD]5[/TD]
[TD]G[/TD]
[TD]E[/TD]
[TD]V[/TD]
[TD]
[/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]F4Q1UX21YFG9
[/TD]
[TD]FALSE[/TD]
[TD]F[/TD]
[TD]4[/TD]
[TD]Q[/TD]
[TD]1[/TD]
[TD]U[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]9[/TD]
[TD]
[/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]J4Q1UX21YFG9[/TD]
[TD]FALSE[/TD]
[TD]J[/TD]
[TD]4[/TD]
[TD]Q[/TD]
[TD]1[/TD]
[TD]U[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]9[/TD]
[TD]
[/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]R4XPNT40TDAS
[/TD]
[TD]FALSE[/TD]
[TD]R[/TD]
[TD]4[/TD]
[TD]X[/TD]
[TD]P[/TD]
[TD]N[/TD]
[TD]T[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]T[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]S[/TD]
[TD]
[/TD]
[TD]4[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]R4XPJJ14QTDA[/TD]
[TD]FALSE[/TD]
[TD]R[/TD]
[TD]4[/TD]
[TD]X[/TD]
[TD]P[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]Q[/TD]
[TD]T[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]
[/TD]
[TD]4[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]MEEM0MMEXYX9
[/TD]
[TD]FALSE[/TD]
[TD]M[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]M[/TD]
[TD]0[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]E[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]X[/TD]
[TD]9[/TD]
[TD]
[/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]JEEM0MMEXYX9[/TD]
[TD]FALSE[/TD]
[TD]J[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]M[/TD]
[TD]0[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]E[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]X[/TD]
[TD]9[/TD]
[TD]
[/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]FTHAAPMLN8JF
[/TD]
[TD]FALSE[/TD]
[TD]F[/TD]
[TD]T[/TD]
[TD]H[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]P[/TD]
[TD]M[/TD]
[TD]L[/TD]
[TD]N[/TD]
[TD]8[/TD]
[TD]J[/TD]
[TD]F[/TD]
[TD]
[/TD]
[TD]2[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]THAAPMLN8JFF[/TD]
[TD]FALSE[/TD]
[TD]T[/TD]
[TD]H[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]P[/TD]
[TD]M[/TD]
[TD]L[/TD]
[TD]N[/TD]
[TD]8[/TD]
[TD]J[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]
[/TD]
[TD]2[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[steedy]

You understanding is correct in the table above, what I ment by relative position is that below has 12 characters all in the string but they are completely in the wrong order, this must fail

ABC DEF GHI JKL

LKJ IHG FED CBA

But like the examples above

ABC DEF GHI JKL

ABC DEF GHI JJJ

This is acceptable as the 9 characters that match are in the same position.

I hope this helps

 

[XOR LX]

But, in your very first post in this thread, you gave this pair as the final of your examples:

FTHAAPMLN8JF
THAAPMLN8JF

and indicated that it should be considered a match.

However, the second of these only has 11 characters; the first 12. And, depending how you define "relative position", it could quite easily be argued that only 1 character matches between those two strings, as here:

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]Position[/TD]
[TD="align: center"]01[/TD]
[TD="align: center"]02[/TD]
[TD="align: center"]03[/TD]
[TD="align: center"]04[/TD]
[TD="align: center"]05[/TD]
[TD="align: center"]06[/TD]
[TD="align: center"]07[/TD]
[TD="align: center"]08[/TD]
[TD="align: center"]09[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD]String 1[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]String 2[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD]Match[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[/TR]
</tbody>[/TABLE]

Of course, you might counter that, in this case, we should compare 2nd string beginning with the 2nd character of the 1st string, viz:

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]Position[/TD]
[TD="align: center"]01[/TD]
[TD="align: center"]02[/TD]
[TD="align: center"]03[/TD]
[TD="align: center"]04[/TD]
[TD="align: center"]05[/TD]
[TD="align: center"]06[/TD]
[TD="align: center"]07[/TD]
[TD="align: center"]08[/TD]
[TD="align: center"]09[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD]String 1[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]String 2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]Match?[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[/TR]
</tbody>[/TABLE]

but then isn't this just rather arbitrary (and also convenient)?

Regards

 

[Rick Rothstein]

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

Did you commit the formula using CTRL+SHIFT+ENTER and not Enter by itself? However, I have to modify my formula because I forgot to test each value's length to make sure they are exactly 12 characters long. Here is my revised array-entered** formula...

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

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

 

[steedy]

I agree but the device that is outputting the codes cannot cope with the 2nd table so in this instance this will be a fail.

-- removed inline image ---

 

[steedy]

Hi Rick this works, however if i paste in to multiple cells it always refers back to cell A1 B1, is there a easy way to paste this formula as I my sheet goes from A1 - A9500 approx and I dont want to have to press Ctrl +shift + Enter on every cell !!!

 

[Rick Rothstein]

Hi Rick this works, however if i paste in to multiple cells it always refers back to cell A1 B1, is there a easy way to paste this formula as I my sheet goes from A1 - A9500 approx and I dont want to have to press Ctrl +shift + Enter on every cell !!!

First of all, I need to modify the formula slightly (I forgot to make the row number in the ROW function call absolute so they don't change when copied). Here is the new formula...

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

Now, assuming you are putting the formula in Column C, select cell C1 and array-enter the formula (as described in my previous messages) into it. Next, type C1:C9500 into the Name Box (the field to the left of the Formula Bar) and hit the Enter key... this will select all the cell you want to put the formula into... then click the Fill button (Home tab, Editing panel) and select Down from the popup menu that appears... that should copy the formula you array-entered in cell C1 down through all the selected cells.