Excel – Compare multiple columns for any two or more cells within the same row having the same content

microsoft excelmicrosoft-excel-2010worksheet-function

I need a formula to compare multiple columns for any two or more cells within the same row having the same content. If that is true, then display "TEXT A" (can be anything such as "TRUE"). If all of the values are different, display "TEXT B" or simply "FALSE".

I'm using an IF formula but it will be time-consuming if there are many columns to compare, so I need a better formula.

=IF((B2=C2);"YES";IF((B2=D2);"YES";IF((C2=D2);"YES";"ALL DIFFERENT")))

The same goes with similar function with OR (resulted in true or false)

=AND(($C2<>$D2);($C2<>$E2);($D2<>$E2))

Below is a screenshot of the worksheet, which is just an example. My actual work has more than 4 columns.

enter image description here

The highlighted rows are the ones where there are two or more cells containing the same text (Group 2 should also be highlighted), hence those should display the "TEXT A" message.

View my Spreadsheet online

Best Answer

This VBa does it (how to add VBa). I have provided a few options so you can scale it in the future, check out the first 12 lines or so where you can type in the various 'answers' . You can choose which is the starting row and ending row, where the results will be displayed and what words to show if there is a text match or not! Please note, the highlighting is due to the Excel Doc you provided, and nothing to do with the code.

Before running a VBa script, take a back up of the file - there is usually no undo option!

Sub DoTheThing()

'Answer these questions or ye walk the plank

Dim row As Integer
row = 2

Dim firstColumn As String
firstColumn = "B"

Dim lastColumn As String
lastColumn = "D"

Dim resultsColumn As String
resultsColumn = "G"

Dim isFoundText As String
isFoundText = "YES"

Dim isNotFoundText As String
isNotFoundText = "Good Job"

'***Below be for the cap'ains eyes only.

Do While Range("A" & row).Value <> ""

    Dim startChar As Integer
    startChar = Asc(firstColumn)

    Dim endChar As Integer
    endChar = Asc(lastColumn)

    Dim i As Integer

    Dim hasMatch As Boolean
    hasMatch = False

    For i = startChar To endChar

    If Range(Chr(i) & row).Value = Range(Chr(i + 1) & row).Value Then

        hasMatch = True

    End If

    If Range(Chr(startChar) & row).Value = Range(Chr(i + 1) & row).Value Then

        hasMatch = True

    End If

    Next i

    If (hasMatch) Then
        Range(resultsColumn & row).Value = isFoundText
    Else
        Range(resultsColumn & row).Value = isNotFoundText
    End If

row = row + 1


Loop

End Sub

I wrote the results to Col G (to keep your original as is)

enter image description here

After the vba is run

enter image description here

Related Question