0

I have a large spreadsheet with possible duplicate rows. However I only care about matching some of the columns as highlighted in yellow below.

enter image description here

So my question is: How can I search for duplicates in only particular columns from a row and when I find a match, highlight them?

7
  • 3
    Pretty ironic question, considering it's been asked a lot of times. Apr 2 '14 at 9:15
  • 1
    Also, I'll leave this here. Apr 2 '14 at 9:26
  • 1
    If row 4 was Blaaah | 2 | Don't care then I assume it's not considered a duplicate?
    – Dave
    Apr 2 '14 at 10:51
  • 1
    @DaveRook Where do I mention that I want to remove them in the question?
    – blarg
    Apr 2 '14 at 10:54
  • 1
    @blarg, in fairness, you didn't, but I think there were a few people who thought that, including myself, Raystafarian and smc! Any way, I've posted an answer which does what you want
    – Dave
    Apr 2 '14 at 11:26
1

This does exactly what you want, based upon the screen shot you've given

Before

enter image description here

And after I click the "highlight the dupes" button

enter image description here

The top part is the bit you can customise. At the moment, I'm looking at Col A and B, but you may want to update that to look at Cols B and C or A and D etc.

I've also provided detail and a link to affect the highlight colour (again, see comments in code)

Sub HighlightDuplicates()

    Dim transparent As Integer
    transparent = -4142 

    Dim yellow As Integer
    yellow = 27 ' colour index, see http://dmcritchie.mvps.org/excel/colors.htm for more details about setting the colour

    Dim column1 As String
    column1 = "A" 'Update me if you don't want to check for dupes in the A column

    Dim column2 As String
    column2 = "B" 'Update me if you don't want to check for dupes in the B column

    Dim endOfRows As Boolean
    moreRows = True

    Dim currentCell As Integer
    currentCell = 0

    Do While (moreRows)

        currentCell = currentCell + 1

        Dim aValue As String
        Dim bValue As String

        aValue = Worksheets("Sheet1").Range(column1 & currentCell).Value
        bValue = Worksheets("Sheet1").Range(column2 & currentCell).Value

        'check it isn't already coloured
        If (Worksheets("Sheet1").Range(column1 & currentCell).Interior.ColorIndex = transparent) Then

            Dim moreInnerRows As Boolean
            moreInnerRows = True

            Dim currentInnerCell As Integer
            currentInnerCell = currentCell

            Dim isDupe As Boolean
            isDupe = False
            'Now to loop through the other rows

            Do While (moreInnerRows)
                currentInnerCell = currentInnerCell + 1

                If (Worksheets("Sheet1").Range(column1 & currentInnerCell).Value = "" And Worksheets("Sheet1").Range(column2 & currentInnerCell).Value = "") Then
                    Exit Do
                End If

                If Worksheets("Sheet1").Range(column1 & currentInnerCell).Value = aValue And Worksheets("Sheet1").Range(column2 & currentInnerCell).Value = bValue Then
                    isDupe = True
                    Worksheets("Sheet1").Range(column1 & currentInnerCell).Interior.ColorIndex = yellow
                    Worksheets("Sheet1").Range(column2 & currentInnerCell).Interior.ColorIndex = yellow

                End If

            Loop

            If (isDupe = True) Then
                'Now we mark the original row as a dupe
                Worksheets("Sheet1").Range(column1 & currentCell).Interior.ColorIndex = yellow
                Worksheets("Sheet1").Range(column2 & currentCell).Interior.ColorIndex = yellow

            End If

        End If

        If (Worksheets("Sheet1").Range(column1 & currentCell).Value = "" And Worksheets("Sheet1").Range(column2 & currentCell).Value = "") Then
            Exit Do
        End If


    Loop

End Sub
1

I would add a new column and use the CONCATENATE column to combine the 2nd and 3rd columns. If your first cell is A1, the formula would be:

=CONCATENATE(B1," ",C1)

Then from the Home ribbon I would go to Conditional Formatting / Highlight Cells Rules / Duplicate Values.

Here's a picture of the final result:

enter image description here

2
  • alternative to concatenate is =B1 & C1 (quicker to type) Mar 7 '17 at 20:50
  • The important takeaway is not the recipe, which will break in time as the software changes, but the core idea: It's much easier to find duplicates in 1 column than many, so combine the many to 1 first. This can also apply to non-table data, and even large data such as images. In this latter case it's impractical to combine them all into a single large binary, so use use something like MD5sum to uniquely identify each and then add those instead. Mar 7 '17 at 21:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.