CODEWITHSUNDEEP
excelvbaexcel-formula
aji
aji

Reputation: 71

Compare two cells with the same word variant

Can anyone help me with an excel formula to look whether one cell with a string of text is the same compare to other cell with the same string of text but in different position/placement.

please see below for example:

cell A1: apple orange grape

cell A2: orange grape apple

I need a formula to check if A1 is the same or not to A2

thanks

Upvotes: 1

Views: 444

Answers (1)

nutsch
nutsch

Reputation: 5962

Put the attached code in a module and call it from your worksheet as a function, e.g.
=sCompare(A1,A2)

CODE

Public Function sCompare(s1 As String, s2 As String) As Boolean
Dim vArr1, vArr2, lLoop As Long, lLoop2 As Long, bMatch As Boolean

vArr1 = Split(trim(s1), " ")
vArr2 = Split(trim(s2), " ")

If UBound(vArr1) <> UBound(vArr2) Then Exit Function

For lLoop = 0 To UBound(vArr1)
    bMatch = False
    For lLoop2 = 0 To UBound(vArr2)
        If vArr1(lLoop) = vArr2(lLoop2) Then
            bMatch = True
            Exit For
        End If
    Next lLoop2

    If bMatch = False Then Exit Function
Next lLoop

sCompare = True
End Function

Upvotes: 3

Related Questions