Excel - Comparison of two strings

[wonderergirl]

Hi all,

I would like to compare two strings together to check if they are same or not. However there are some conditions where the two strings are considered same.

Conditions where both strings are the same:
1) "Bear Care Co" & "Bear Care"
2) "Bear Care" & "Bear Care Co"
3) "Bear Group" & "Bear Corp"
4) "Dog/Puppy" & "Puppy/Dog"
5) "Inter Study Group" & "ISG"
6) "Cat/Kitten" & "Kitten"

Is it possible to do so in VBA code? Thanks in advance

 

[WarPigl3t]

Hello again woderergirl. I helped you before. I remember your name. Yes it is possible. Anything is possible. The many arguments you provided would require a lot of code. I don't have time to do it tonight. I'll do it for you, but you have to give me more info. And please provide more info in your future posts. I need to know the range that this data is in. You said you want to compare it. Awesome. But you didn't say what your output should be. If the first string partially matches the second string, what should the output be? Where should the output go? Do you want a Sub, a function, or an on change event? Does case matter(upper and lower case)? Is the data in 1 column? You left out way too much information. That's the primary reason threads go without replies.

 

[wonderergirl]

Hello WarPiglet, sorry for the lack of information. I would take notice on my future posts.

1. It would be good if it is a function. The arguments will be the two strings, string1 and string 2 and the function will return "True" or "False" back to the main Sub.
If the conditions above is met then it would be a "TRUE".

2. The data that I would like to compare, string1 and string 2 are in column "B" and column "F" respectively. There will be a header on the first row across the two columns, so the data will start from Row 2.

3. It is a case insensitive, so upper or lower case does not matter.

 

[WarPigl3t]

Okay I got all the logic done. I just need some help with the syntax. So if anyone else can help me with this code, that would be awesome. I'm having trouble with the part I colored in red. It doesn't want to take that code whenever the data doesn't have a "/" slash in the value. So it does take it only if there is a "/" like in this example. "Dog/Puppy"

Function myFunction(a, b)
    output = False
    [COLOR=#008000]'Trying with IsError[/COLOR]
[COLOR=#ff0000]    If Application.WorksheetFunction.IsError( _
        Application.WorksheetFunction.Search("/", a, 1)) = False Then
        a = Replace(a, "/", " ")
    End If[/COLOR]
    [COLOR=#008000]'Trying with IfError[/COLOR]
[COLOR=#ff0000]    If Application.WorksheetFunction.IfError( _
    Application.WorksheetFunction.Search("/", b, 1), "error") <> "error" Then
        b = Replace(b, "/", " ")
    End If[/COLOR]
    a = Split(a, " ")
    b = Split(b, " ")
    i = 0
    For Each i In a(i)
        AbrevA = AbrevA & Left(a(i), 1)
        i = i + 1
    Next
    i = 0
    For Each i In b(i)
        AbrevB = AbrevB & Left(b(i), 1)
        i = i + 1
    Next
    If AbrevA = b(0) Or AbrevB = a(0) Then
        output = True
    End If
    i = 0
    For Each i In a(i)
        ii = 0
        For Each ii In b(ii)
            If a(i) = b(ii) Then
                output = True
            End If
            ii = ii + 1
        Next
        i = i + 1
    Next
    myFunction = output
End Function

[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]header
[/TD]
[TD]header[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Bear Care[/TD]
[TD]Bear Care Co[/TD]
[TD]=myFunction(B2, F2)
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]Bear Group[/TD]
[TD]Bear Corp[/TD]
[TD]True
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]Dog/Puppy
[/TD]
[TD]Puppy/Dog[/TD]
[TD]True
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]Inter Study Group[/TD]
[TD]ISG[/TD]
[TD]True
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]Cat/Kitten[/TD]
[TD]Kitten[/TD]
[TD]True
[/TD]
[/TR]
</tbody>[/TABLE]

 

[WarPigl3t]

Alright so I realized a lot of my code was not needed. So here is what I have so far. I'm having trouble with what I highlighted in red.

Function myFunction(a, b)
    a = Replace(a, "/", " ")
    b = Replace(b, "/", " ")
    a = Split(a, " ")
    b = Split(b, " ")
    [COLOR=#ff0000]i = 0
    For Each i In a
        AbrevA = AbrevA & Left(a(i), 1)
    Next i
    i = 0
    For Each i In b(i)
        AbrevB = AbrevB & Left(b(i), 1)
        i = i + 1
    Next[/COLOR]
    If AbrevA = b(0) Or AbrevB = a(0) Then
        myFunction = True
        Exit Function
    End If
    i = 0
    For Each i In a(i)
        ii = 0
        For Each ii In b(ii)
            If a(i) = b(ii) Then
                myFunction = True
                Exit Function
            End If
            ii = ii + 1
        Next
        i = i + 1
    Next
End Function

 

[WarPigl3t]

I am a VBA God!!! SOLVED

Function myFunction(a, b)
    a = LCase(a)
    b = LCase(b)
    a = Replace(a, "/", " ")
    b = Replace(b, "/", " ")
    a = Split(a, " ")
    b = Split(b, " ")
    c = 0
    For Each i In a
        AbrevA = AbrevA & Left(a(c), 1)
        c = c + 1
    Next i
    c = 0
    For Each i In b
        AbrevB = AbrevB & Left(b(c), 1)
        c = c + 1
    Next i
    If AbrevA = b(0) Or AbrevB = a(0) Then
        myFunction = True
        Exit Function
    End If
    
    For Each i In a
        For Each ii In b
            If i = ii Then
                myFunction = True
                Exit Function
            End If
        Next ii
    Next i
    myFunction = False
End Function

 

[wonderergirl]

Thank you very much for the code. It work very well with my data.