Duplicated Lines in a Table

default_name

Board Regular
Joined
May 16, 2018
Messages
170
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hey everyone!

I have a large table which has many columns (from A to CU).
I have been populating this table with data from several different places (same format).
There are occasionally some lines/rows in the table that are duplicates.
Is there a way to identify these duplicated rows and to have them copied into a blank spreadsheet so that I may review them?

Hope that makes sense.

It's something like this:
In this case, rows 2 and 5 are identical....is there a way to extract these rows (remove them from this spreadsheet) and to paste them into a blank spreadsheet?

ABCD. . . . . . . . . . . .CU
1Header1Header2Header3Header4. . . . . . . . . . . .FinalHeader
2CheeseBlue3.56Up. . . . . . . . . . . .Yes
3GrainGreen3.8Up. . . . . . . . . . . .Yes
4CarrotPurple5.95Left. . . . . . . . . . . .No
5CheeseBlue3.56Up. . . . . . . . . . . .Yes
6MeatRed1.22Left. . . . . . . . . . . .Yes
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi default_name,
there are several ways to do that. If you want it all automated you can try writing VBA/a macro for that. If you want to do it manually: you could use the "remove duplicates" function (in the Data menu) to remove them, but then they are gone without the new sheet having the records stored. So you could first copy all your data, after that remove duplicates and match that "removed duplicate copy" with your "original copy" to find out which lines have been removed.
Hope that gets you started,
Koen
 
Upvote 0
Thanks Rijnsent

I have been playing around with the Remove Duplicates function, but to no avail.
Instead of copying the data into another sheet, what VBA code would work to just highlight the duplicated rows (perhaps yellow) instead?
 
Upvote 0
Thanks! That really helped!

Here's the code I was able to compile for this. It works!
....but Im thinking there might be a more efficient way of doing the loops (instead of nesting a ton of IF statements).
Thoughts?
VBA Code:
Sub highlightDuplicateRows()
    Dim lastRow As Integer, compRow As Integer, rowNo As Integer
    
    'Get the last row of data
    lastRow = Sheet1.Range("A1").CurrentRegion.Rows.Count

    'Loop through all the rows
    For rowNo = 2 To lastRow
    
        'For each rowNo, loop through all the remaining rows
        For compRow = rowNo + 1 To lastRow
        
            'Check if a match is found in Column A for the current rowNo
            If Range("A" & compRow) = Range("A" & rowNo) Then
                If Range("B" & compRow) = Range("B" & rowNo) Then
                    If Range("C" & compRow) = Range("C" & rowNo) Then
                        If Range("D" & compRow) = Range("D" & rowNo) Then
                            If Range("E" & compRow) = Range("E" & rowNo) Then
                                If Range("F" & compRow) = Range("F" & rowNo) Then
                                    If Range("G" & compRow) = Range("G" & rowNo) Then
                                        If Range("H" & compRow) = Range("H" & rowNo) Then
                                            If Range("I" & compRow) = Range("I" & rowNo) Then
                                                If Range("J" & compRow) = Range("J" & rowNo) Then
                                                    If Range("K" & compRow) = Range("K" & rowNo) Then
                                                        If Range("L" & compRow) = Range("L" & rowNo) Then
                                                            If Range("M" & compRow) = Range("M" & rowNo) Then
                                                                If Range("N" & compRow) = Range("N" & rowNo) Then
                                                                    If Range("O" & compRow) = Range("O" & rowNo) Then
                                                                        If Range("P" & compRow) = Range("P" & rowNo) Then
                                                                            If Range("Q" & compRow) = Range("Q" & rowNo) Then
                                                                                If Range("R" & compRow) = Range("R" & rowNo) Then
                                                                                    If Range("S" & compRow) = Range("S" & rowNo) Then
                                                                                        If Range("T" & compRow) = Range("T" & rowNo) Then
                                                                                            If Range("U" & compRow) = Range("U" & rowNo) Then
                                                                                                If Range("V" & compRow) = Range("V" & rowNo) Then
                                                                                                    If Range("W" & compRow) = Range("W" & rowNo) Then
                                                                                                        If Range("X" & compRow) = Range("X" & rowNo) Then
                                                                                                            If Range("Y" & compRow) = Range("Y" & rowNo) Then
                                                                                                                If Range("Z" & compRow) = Range("Z" & rowNo) Then
                                                                                                                    If Range("AA" & compRow) = Range("AA" & rowNo) Then
                                                                                                                        If Range("AB" & compRow) = Range("AB" & rowNo) Then
                                                                                                                            If Range("AC" & compRow) = Range("AC" & rowNo) Then
                                                                                                                                If Range("AD" & compRow) = Range("AD" & rowNo) Then
                                                                                                                                    If Range("AE" & compRow) = Range("AE" & rowNo) Then
                                                                                                                                        If Range("AF" & compRow) = Range("AF" & rowNo) Then
                                                                                                                                            If Range("AG" & compRow) = Range("AG" & rowNo) Then
                                                                                                                                                If Range("AH" & compRow) = Range("AH" & rowNo) Then
                                                                                                                                                    If Range("AI" & compRow) = Range("AI" & rowNo) Then
                                                                                                                                                        If Range("AJ" & compRow) = Range("AJ" & rowNo) Then
                                                                                                                                                            If Range("AK" & compRow) = Range("AK" & rowNo) Then
                                                                                                                                                                If Range("AL" & compRow) = Range("AL" & rowNo) Then
                                                                                                                                                                    If Range("AM" & compRow) = Range("AM" & rowNo) Then
                                                                                                                                                                        If Range("AN" & compRow) = Range("AN" & rowNo) Then
                                                                                                                                                                            If Range("AO" & compRow) = Range("AO" & rowNo) Then
                                                                                                                                                                                If Range("AP" & compRow) = Range("AP" & rowNo) Then
                                                                                                                                                                                    If Range("AQ" & compRow) = Range("AQ" & rowNo) Then
                                                                                                                                                                                        If Range("AR" & compRow) = Range("AR" & rowNo) Then
                                                                                                                                                                                            If Range("AS" & compRow) = Range("AS" & rowNo) Then
                                                                                                                                                                                                If Range("AT" & compRow) = Range("AT" & rowNo) Then
                                                                                                                                                                                                    If Range("AU" & compRow) = Range("AU" & rowNo) Then
                                                                                                                                                                                                        If Range("AV" & compRow) = Range("AV" & rowNo) Then
                                                                                                                                                                                                            If Range("AW" & compRow) = Range("AW" & rowNo) Then
                                                                                                                                                                                                                If Range("AX" & compRow) = Range("AX" & rowNo) Then
                                                                                                                                                                                                                    If Range("AY" & compRow) = Range("AY" & rowNo) Then
                                                                                                                                                                                                                        If Range("AZ" & compRow) = Range("AZ" & rowNo) Then
                                                                                                                                                                                                                            If Range("BA" & compRow) = Range("BA" & rowNo) Then
                                                                                                                                                                                                                                If Range("BB" & compRow) = Range("BB" & rowNo) Then
                                                                                                                                                                                                                                    If Range("BC" & compRow) = Range("BC" & rowNo) Then
                                                                                                                                                                                                                                        If Range("BD" & compRow) = Range("BD" & rowNo) Then
                                                                                                                                                                                                                                            If Range("BE" & compRow) = Range("BE" & rowNo) Then
                                                                                                                                                                                                                                                If Range("BF" & compRow) = Range("BF" & rowNo) Then
                                                                                                                                                                                                                                                    If Range("BG" & compRow) = Range("BG" & rowNo) Then
                                                                                                                                                                                                                                                        If Range("BH" & compRow) = Range("BH" & rowNo) Then
                        'Duplicate data accross 3 columns found. Highlight both the rows
                        Range("A" & compRow & ":BH" & compRow).Interior.Color = vbYellow
                        Range("A" & rowNo & ":BH" & rowNo).Interior.Color = vbYellow
                                                                                                                                                                                                                                                        End If
                                                                                                                                                                                                                                                    End If
                                                                                                                                                                                                                                                End If
                                                                                                                                                                                                                                            End If
                                                                                                                                                                                                                                        End If
                                                                                                                                                                                                                                    End If
                                                                                                                                                                                                                                End If
                                                                                                                                                                                                                            End If
                                                                                                                                                                                                                        End If
                                                                                                                                                                                                                    End If
                                                                                                                                                                                                                End If
                                                                                                                                                                                                            End If
                                                                                                                                                                                                        End If
                                                                                                                                                                                                    End If
                                                                                                                                                                                                End If
                                                                                                                                                                                            End If
                                                                                                                                                                                        End If
                                                                                                                                                                                    End If
                                                                                                                                                                                End If
                                                                                                                                                                            End If
                                                                                                                                                                        End If
                                                                                                                                                                    End If
                                                                                                                                                                End If
                                                                                                                                                            End If
                                                                                                                                                        End If
                                                                                                                                                    End If
                                                                                                                                                End If
                                                                                                                                            End If
                                                                                                                                        End If
                                                                                                                                    End If
                                                                                                                                End If
                                                                                                                            End If
                                                                                                                        End If
                                                                                                                    End If
                                                                                                                End If
                                                                                                            End If
                                                                                                        End If
                                                                                                    End If
                                                                                                End If
                                                                                            End If
                                                                                        End If
                                                                                    End If
                                                                                End If
                                                                            End If
                                                                        End If
                                                                    End If
                                                                End If
                                                            End If
                                                        End If
                                                    End If
                                                End If
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        Next compRow
    Next rowNo
End Sub
 
Upvote 0
Wow! Simply wow! That's a lot of IF statements...

What you could do is make an extra loop for the columns, like so:

VBA Code:
For rowNo = 2 To lastRow

        'For each rowNo, loop through all the remaining rows

        For compRow = rowNo + 1 To lastRow
            'Start comparing rows, first set the matches to 0
            MatchCols = 0
            NrCols = 55
            For Col = 1 to NrCols
                If Cells(compRow, Col) = Cells(rowNo, Col) Then
                    'Equal, add one to the result
                    MatchCols = MatchCols + 1
                Else
                    'Column difference, jump out of the loop (no need to compare other columns)
                    Exit For
                End If
            next Col

            If NrCols = MatchCols Then
                'All columns match
                'Duplicate data accross 3 columns found. Highlight both the rows
                Range("A" & compRow & ":BH" & compRow).Interior.Color = vbYellow
                Range("A" & rowNo & ":BH" & rowNo).Interior.Color = vbYellow                
            end if
Next compRow   

Next rowNo
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top