3

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

9
  • Your question reads as if we're a script writing service. How many columns are there? If only a few, then you may need a long function like this. Or, learn some VBa
    – Dave
    Dec 4, 2014 at 8:39
  • What do you mean by "text a" and "text b" Are these literal phrases you want to display or do they refer to specific cell values? Nothing in your example appears to display this requirement in column F. Is column E highlighted because that's where you want this result? How many columns are there to compare (3 in example but the question says many)? It this simply a logical test (any two or more = true), or do you need to identify the text or columns?
    – fixer1234
    Dec 4, 2014 at 8:52
  • @Dave sorry about that , I just didn't want to make the title too long Dec 4, 2014 at 9:49
  • @fixer1234 text a text b are just any words that I need, hence column F contains ("YES" and "Good job") They are highlighted because those are the columns where there are two or more cells that contain same value. There are about 5 to 6 columns for my work but for the example I just used 3. Yes two or more cells have same value and no need to identify certain text in the cells. As long as there are two or more cells containing same words or numbers then it will produce "Text A" Dec 4, 2014 at 9:52
  • @Daveboth both my title and details say that I need to compare cells from DIFFERENT COLUMNS but on the SAME ROW. Any help is really appreciated. Dec 4, 2014 at 9:57

7 Answers 7

1

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

1
  • Thanks but I cannot see any of the attached image Dec 5, 2014 at 8:27
0

There is a straightforward way to do this with formulas. Let me point out that the example shows cases that only involve matches that include the first column. Any formula should be tested against an example that contains random matches.

I'll explain this in two parts since the number of columns can vary. The first part is an expression that you build for the required number of columns. I've added a column of data to illustrate the formula better, and included some cases with random matches:

enter image description here

The expression for four columns of data looks like this (shown for row 2, the first data row):

    COUNTIF(C2:E2,B2)+COUNTIF(D2:E2,C2)+(D2=E2)

If N is the number of data columns, the formula contains N-1 terms. The first term counts how many columns have values that equal the first. The second counts how many of the remaining columns equal the second. And so on. They can all be COUNTIFs but I've used as simpler term for the last case. Instead of counting one column, I just test whether the next to last equals the last. A result of True is recognized as a value of 1, False as a value of 0. If all of the values are unique, this expression will equal 0. Otherwise, it will be a higher number. The expression goes inside an IF test:

    =IF(<expression>=0,"Unique Message","Match Message")

So for four data columns, the formula would be:

    =IF(COUNTIF(C2:E2,B2)+COUNTIF(D2:E2,C2)+(D2=E2)=0,"Unique Message","Match Message")

The result looks like this:

enter image description here

3
  • sorry I cannot see the picture Dec 5, 2014 at 8:25
  • Does <expression> get longer the more column I have? Dec 5, 2014 at 8:32
  • @ fixer1234 I got the answer myself after I tried using it, it got longer the more columns I have, but at least it is more simple than using my IF function and OR function. Thanks a lot! Dec 5, 2014 at 8:38
0

I would use the Power Query Add-In for this. It has a Group command with a Count Distinct function that can figure this out across any combination of rows and/or columns.

I've built a prototype which you can view or download - its "Power Query demo - compare multiple columns for unique values.xlsx" in my One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Note you can keep adding columns and/or rows to the Input sheet, then just hit Refresh All from the Data ribbon to reprocess the Power Queries.

90% of this was built just clicking around in the Power Query ribbon. The only exception was the simple "if" statement I wrote in the last step of the "Compare Multiple Columns" query. So there is far less code to maintain, compared to either a formula or VBA solution.

0

For cell F2:

=IF(SUMPRODUCT(--(FREQUENCY(MATCH(B2:D2,B2:D2,0),COLUMN(B2:D2)-COLUMN(B2)+1)>0))=3,TRUE,FALSE)

This will give you True whenever there are three distinct values in those three columns.

Adapted from a site which includes more explanation and an option for dealing with blanks: https://exceljet.net/formula/count-unique-text-values-in-a-range

The same site touches on countifs and the potential for performance problems with large sets of data: https://exceljet.net/formula/count-unique-values-in-a-range-with-countif

0

Two-step solution without array formulas:

Excel Screenshot

Step 1: For each column calculate number of occurences. If it's greater than 1 then put 1, otherwise empty string. Formula for cell D2 on the screenshot:

=IF(COUNTIF($A2:$C2,A2)>1,1,"")

This formula can be dragged (or double-clicked) by the bottom right corner to cover all rows, then the whole selected row D can be dragged by the bottom right corner to cover all columns.

Step 2: For each row check if there was any "1"

Formula for cell G2 on the screenshot:

=IF(SUM($D2:$F2)>0,"YES","Good Job")

This formula can be dragged (or double-clicked) by the bottom right corner to cover all rows.

In order to make it nicer, you might consider moving the intermediate calculations to another sheet.

0

Formula for D2 on the screenshot:

=IF(MAX(COUNTIF($A2:$C2, $A2:$C2))>1,"YES","Good job")

Enter it as an array formula by pressing Ctrl+Shift+Enter when editing the formula. After that it can be dragged (or double-clicked) by the bottom right corner to cover all rows.

enter image description here

-3

You can use this formula:

=IF(Cell Number > 2nd cell Number, result)
2
  • 3
    This is a little too cryptic to be useful. Can you elaborate (maybe add a more concrete example)? Thanks.
    – fixer1234
    Feb 13, 2017 at 15:52
  • 1
    Also, in deference to the non-native English speakers and due to the semi-professional nature of this site, please spell out words such as "you" and "be" completely. Feb 13, 2017 at 17:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .