String Comparison Help

[paulcianf]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CompanyCode
[/TD]
[TD]CompanyCode[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]112 New York asq
[/TD]
[TD]442 New York abd
[/TD]
[TD]Pass
[/TD]
[/TR]
[TR]
[TD]334 San Fran wws
[/TD]
[TD]252 Kansas 1saq
[/TD]
[TD]Fail
[/TD]
[/TR]
[TR]
[TD]210 St.Louis qes
[/TD]
[TD]088 St.Louis abc
[/TD]
[TD]Pass
[/TD]
[/TR]
</tbody>[/TABLE]

Hi there,
I am looking for help on creating a string for my spreadsheet. The table above is a small sample size of what I require. Essentially, if any 7 character string in column A matches column B, I need column C to show Pass. And if there is no match, it should saw Fail. So for the first example, even though the numbers and letters do not match, because it has the New York it results in a pass. Any help is appreciated.
Thanks

 

[Momentman]

Something like this

Excel 2010
	A	B	C
1	112 New York asq	442 New York abd	Pass
2	334 San Fran wws	252 Kansas 1saq	Fail
3	210 St.Louis qes	088 St.Louis abc	Pass
Sheet9

Cell Formulas
Range	Formula
C1	{=IF(SUM(IFERROR(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-6)),7),B1),0))>0,"Pass","Fail")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[paulcianf]

Something like this

Excel 2010

	A	B	C
Pass
Fail
Pass

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]112 New York asq[/TD]
[TD="bgcolor: #FAFAFA"]442 New York abd[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]334 San Fran wws[/TD]
[TD="bgcolor: #FAFAFA"]252 Kansas 1saq[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]210 St.Louis qes[/TD]
[TD="bgcolor: #FAFAFA"]088 St.Louis abc[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]{=IF(SUM(IFERROR(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-6)),7),B1),0))>0,"Pass","Fail")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Awesome thanks so much!

 

[WarPigl3t]

lastRow = Range("A" & Rows.Count).End(xlup).Row
i = 2
Do Until i > lastRow
    myAValue = Range("A" & i).Value
    myBValue = Range("B" & i).Value
    myALen = Len(myAValue)
    matchCounter = 0
    ii = 1
    Do Until ii > myALen
        myAChr = MID(myAValue, ii, 1)
        Do Until iii > myBLen
            myBChr = MID(myBValue, iii, 1)
            If myAChr = myBChr Then
                matchCounter = matchCounter + 1
                Exit Do
            End If
            iii = iii + 1
        Loop
        ii = ii + 1
    Loop
    If matchCounter > 7 Then
        Range("C" & i).Value = "Pass"
    Else
        Range("C" & i).Value = "Fail"
    End If
    i = i + 1
Loop

Here's how the code works.
The first Do Loop says Run this code until it evaluates ever row in the table.
The second Do Loop says Run this code until it evaluates every character in the value of the A column in the row number it is currently evaluating.
The third Do Loop says Run this code until it evaluates every character in the value of the B column in the row number it is currently evaluating.
The first If statement checks to see if the currently evaluating character of the A value matches the currently evaluating character of the B column. If it matches, it adds 1 to the counter and exits 1 Do Loop that it is in because it already found a match. The counter should be greater than 7 for it to pass.

Here's the flaw in this logic: Lets say you have a string in A2 that says "Hi Hello" and a string in cell B2 that says "Hi Jelly". Us humans can see that the second "H" in "Hi Hello" does not match the "J" in "Hi Jelly". The "o" in "Hi Hello" does not match the "y" in "Hi Jelly". So that means that there are only 6 matching characters because a space in the text counts as a character. This test should Fail, but it doesn't. And here's why. The logic I created in my code will match the first "H" in "Hi Hello" with the first "H" in "Hi Jelly" as it should. But then it will later match the second "H" in "Hi Hello" to the "H" in "Hi Jelly". This will cause the matchings to reach 7. So this test will Pass in the code I gave you. Using this logic, I can't get it any better. This is beyond me.

Now I do have another idea but I'd have to ask you something before I make the code. Lets look at your table in cells A2 that says "112 New York asq" and B2 that says "442 New York abd". New York matches New York in both columns. But what if B2 said ewN orkY? Would that be considered a match? Because it does have those characters in A2 only in different order. If they are not considered a match then I can most definately create code that looks for consecutive 7 character matches.

 

[mikerickson]

How about
=IF(SUMPRODUCT(--(COUNTIF(B:B,"*"&MID(A1&REPT(CHAR(5),255),COLUMN(A1:AZ1),7)&"*")>0))>1,"pass","Fail")

 

[shg]

I like MM's and Mike's solutions, but I had this laying around, so ...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

CompanyCode​

[/td][td]

CompanyCode​

[/td][td]

Common Substring​

[/td][td]

P/F​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]112 New York asq[/td][td]442 New York abd[/td][td]2 New York a[/td][td]P[/td][td]C2: =LCS(A2, B2)[/td][/tr]

[tr][td]

3​

[/td][td]334 San Fran wws[/td][td]252 Kansas 1saq[/td][td]an[/td][td]F[/td][td]D2: =IF(LEN(C2)>=7, "P","F")[/td][/tr]

[tr][td]

4​

[/td][td]210 St.Louis qes[/td][td]088 St.Louis abc[/td][td] St.Louis [/td][td]P[/td][td][/td][/tr]
[/table]

Function LCS(ByVal s1 As String, ByVal s2 As String, _
             Optional bCaseSensitive As Boolean = False) As String
    ' shg 2009/2010
    ' Returns the Longest Common Substring in s1 and s2

    ' Max steps = Len(shorter) * (Len(shorter) + 1) / 2

    Dim iComp       As VbCompareMethod
    Dim iLen        As Long     ' match length
    Dim iBeg        As Long     ' where match begins in s1
    Dim sT          As String   ' temp string for swapping

    If bCaseSensitive Then iComp = vbBinaryCompare Else iComp = vbTextCompare
    ' swap if necessary so s1 is the longer string
    If Len(s1) < Len(s2) Then: sT = s1: s1 = s2: s2 = sT

    For iLen = Len(s2) To 1 Step -1
        For iBeg = 1 To Len(s2) - iLen + 1
            LCS = Mid(s2, iBeg, iLen)
            If InStr(1, s1, LCS, iComp) Then Exit Function
        Next iBeg
    Next iLen

    LCS = vbNullString
End Function