Excel - Comparison of two strings

wonderergirl

New Member
Joined
Aug 3, 2014
Messages
28
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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"
Code:
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
B
F
G
1
header
header
2
Bear CareBear Care Co=myFunction(B2, F2)
3
Bear GroupBear CorpTrue
4
Dog/Puppy
Puppy/DogTrue
5
Inter Study GroupISGTrue
6
Cat/KittenKittenTrue

<tbody>
</tbody>
 
Upvote 0
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.
Code:
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
 
Upvote 0
I am a VBA God!!! SOLVED
Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,203,655
Messages
6,056,571
Members
444,877
Latest member
kat517

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top