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>

 

[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.

CODE

According to you

1

2

3

4

5

6

7

8

9

10

11

12

* NOM

According to me

F0FXE98MUBQF

FALSE

F

0

F

X

E

9

8

M

U

B

Q

F

10

TRUE

J0FXE98MJBQF

FALSE

J

0

F

X

E

9

8

M

J

B

Q

F

10

TRUE

1ZP0XDJYKG5K

TRUE

1

Z

P

0

X

D

J

Y

K

G

5

K

12

TRUE

1ZP0XDJYKG5K

TRUE

1

Z

P

0

X

D

J

Y

K

G

5

K

12

TRUE

1NW3FWG9CWKE

FALSE

1

N

W

3

F

W

G

9

C

W

K

E

11

TRUE

1JW3FWG9CWKE

FALSE

1

J

W

3

F

W

G

9

C

W

K

E

11

TRUE

N5W4D9WXJKPB

FALSE

N

5

W

4

D

9

W

X

J

K

P

B

11

TRUE

J5W4D9WXJKPB

FALSE

J

5

W

4

D

9

W

X

J

K

P

B

11

TRUE

291SN1Y8V5AN

TRUE

2

9

1

S

N

1

Y

8

V

5

A

N

12

TRUE

291SN1Y8V5AN

TRUE

2

9

1

S

N

1

Y

8

V

5

A

N

12

TRUE

F7M61Q0J5GEV

FALSE

F

7

M

6

1

Q

0

J

5

G

E

V

11

TRUE

J7M61Q0J5GEV

FALSE

J

7

M

6

1

Q

0

J

5

G

E

V

11

TRUE

F4Q1UX21YFG9

FALSE

F

4

Q

1

U

X

2

1

Y

F

G

9

11

TRUE

J4Q1UX21YFG9

FALSE

J

4

Q

1

U

X

2

1

Y

F

G

9

11

TRUE

R4XPNT40TDAS

FALSE

R

4

X

P

N

T

4

0

T

D

A

S

4

FALSE

R4XPJJ14QTDA

FALSE

R

4

X

P

J

J

1

4

Q

T

D

A

4

FALSE

MEEM0MMEXYX9

FALSE

M

E

E

M

0

M

M

E

X

Y

X

9

11

TRUE

JEEM0MMEXYX9

FALSE

J

E

E

M

0

M

M

E

X

Y

X

9

11

TRUE

FTHAAPMLN8JF

FALSE

F

T

H

A

A

P

M

L

N

8

J

F

2

FALSE

THAAPMLN8JFF

FALSE

T

H

A

A

P

M

L

N

8

J

F

F

2

FALSE

<tbody>
</tbody>

 

[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:

Position

01

02

03

04

05

06

07

08

09

10

11

12

String 1

F

T

H

A

A

P

M

L

N

8

J

F

String 2

T

H

A

A

P

M

L

N

8

J

F

Match

N

N

N

Y

N

N

N

N

N

N

N

N

<tbody>
</tbody>

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

Position

01

02

03

04

05

06

07

08

09

10

11

12

String 1

F

T

H

A

A

P

M

L

N

8

J

F

String 2

T

H

A

A

P

M

L

N

8

J

F

Match?

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

<tbody>
</tbody>

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.