Comparing 2 cells....

[Spajeoly]

Alright, I know how I would do this in VBA. But, I was curious to see if anyone knew of a formula I could use to compare the values of 2 cells to see how similar they are.

I was unable to find any such function that could be used in formulas, so I will just go ahead and work on my VBA function.

But, I'll check back and see if anyone has anything for this.

Thanks.

[RobDog888]

Wouldnt it be just an IF comparison?

=IF(A2=A3,"Match","NoMatch")

[Spajeoly]

Well, I can do that... But what I need to know is if there are any similarities.... More like if they have any common words.

I can do it easily in VBA, but, just... I am LAZY right now.

So say you take:

Muffins are very good

And

Muffins are not very good at all.

Compare them and, well, figure out how much they have in common. But, as I said... I seriously doubt there is a way to do this with formulas......

[RobDog888]

Oh more like a fuzzy match comparison. Hmm, nope nothing built in for that.

Probably just split the cell into an array split on the space char?
Then loop comparing the target cell with an Instr function comparison.
Or maybe a .Find method passing the array element.

I'm feeling lazy too. Was out at a clients site today. Time for a nap

[Spajeoly]

Haha, good reply, that's exactly what I am doing and it is working great thus far. Now time for some statistical crap and we're ready!

Thanks a lot.

[malik641]

It's funny you guys mention this, I just gave someone some code exactly how RobDog described....

Say you have your main values on sheet "Blad1" in column B and your new (or comparison) values on sheet "Blad2" in Column B.....

I know you said you got it working, but just to compare:

Option Explicit
Sub CompareStrings()
    If TypeName(Selection) <> "Range" Then Exit Sub
    Dim wsBlad1 As Excel.Worksheet, wsBlad2 As Excel.Worksheet
    Dim cell As Excel.Range, cellMain As Excel.Range
    Dim rngBlad1 As Excel.Range, rngBlad2 As Excel.Range
    Dim strMain As String, strCompare As String
    Dim SplitMain As Variant, SplitCompare As Variant
    Dim i As Long, j As Long, ItemMatch As Long, count As Long
    Dim Percents() As Single, StringMain() As String
    Dim StringCompare() As String
    Dim sPercent As Single
    ReDim Percents(0)
    ReDim StringMain(0)
    ReDim StringCompare(0)
    Set wsBlad1 = Worksheets("Blad1")
    Set wsBlad2 = Worksheets("Blad2")
    Set rngBlad1 = wsBlad1.Range("B2:B" & wsBlad1.Cells(Rows.count, "B").End(xlUp).Row)
    Set rngBlad2 = wsBlad2.Range("B2:B" & wsBlad2.Cells(Rows.count, "B").End(xlUp).Row)
    For Each cell In rngBlad2
        SplitCompare = Split(WorksheetFunction.Trim(cell.Text), " ")
         
        For Each cellMain In rngBlad1
            SplitMain = Split(WorksheetFunction.Trim(cellMain.Text), " ")
            ItemMatch = 0
             
            For i = LBound(SplitCompare) To UBound(SplitCompare)
                For j = LBound(SplitMain) To UBound(SplitMain)
                    If UCase(SplitCompare(i)) = UCase(SplitMain(j)) Then
                        ItemMatch = ItemMatch + 1
                        Exit For
                    End If
                Next
            Next
             
            sPercent = Round(((ItemMatch) / (UBound(SplitMain) + 1)) * 100, 2)
             
             'Exit Sub
            If sPercent > 0 Then
                 
                ReDim Preserve Percents(0 To count)
                ReDim Preserve StringMain(0 To count)
                ReDim Preserve StringCompare(0 To count)
                 
                Percents(count) = sPercent
                StringMain(count) = cellMain.Text
                StringCompare(count) = cell.Text
                count = count + 1
            End If
             
            If sPercent = 100 Then
                Exit For 'May not want this...but unsure
            End If
        Next cellMain
    Next cell
    With Sheets("Blad3")
        .Cells.Clear
         
        .Range("A1") = "Percent"
        .Range("B1") = "Main Database Text"
        .Range("C1") = "New data text"
         
        .Range("A2:A" & count + 1).Value = WorksheetFunction.Transpose(Percents)
        .Range("B2:B" & count + 1).Value = WorksheetFunction.Transpose(StringMain)
        .Range("C2:C" & count + 1).Value = WorksheetFunction.Transpose(StringCompare)
    End With
End Sub

[Spajeoly]

That is essentially what I did... A little less code however. It's simple and works nicely.

Thanks for all the replies.