How to Check DUPLICATE Rows

NEW_2VBA

Board Regular
Joined
Dec 15, 2015
Messages
85
Is there a macro or other Excel resource I can use to check for duplicate values entered in a row?

Each row contains about 15 cells so I need to know if there are any instances of all 15 cells being identical.

Some information will be duplicated however we're trying to identify the line entries that are exactly alike in all 15 cells within a spreadsheet containing sometimes over 5K+ rows of data. Thanks! Here's a condensed example:
InvoiceDateJobNameDescriptionAmount
1456A4/11/18AnalystJohn DoeProfessional Services$800
1456A4/11/18AnalystJohn DoeProfessional Services$800

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
What do you want to do with the duplicate entries?
 

NEW_2VBA

Board Regular
Joined
Dec 15, 2015
Messages
85
Highlight them or identify them in some way so that we can change them to make them unique.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
Ok, how about
Code:
Sub FindDuplicateRows()

   Dim Cl As Range
   Dim ValU As String
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.count).End(xlUp))
         ValU = Join(Application.Transpose(Application.Transpose(Cl.Resize(, 15).Value)), "|")
         If Not .exists(ValU) Then
            .Add ValU, Cl
         Else
            Cl.Resize(, 15).Interior.Color = 45678
            .Item(ValU).Resize(, 15).Interior.Color = 45678
         End If
      Next Cl
   End With
End Sub
 

NEW_2VBA

Board Regular
Joined
Dec 15, 2015
Messages
85
It worked like a charm!! THANK YOU!! Is it possible to highlight orange?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
Change the color to 49407
 

NEW_2VBA

Board Regular
Joined
Dec 15, 2015
Messages
85
Thanks again! I really appreciate it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Is there a macro or other Excel resource I can use to check for duplicate values entered in a row?

Each row contains about 15 cells so I need to know if there are any instances of all 15 cells being identical.

Some information will be duplicated however we're trying to identify the line entries that are exactly alike in all 15 cells within a spreadsheet containing sometimes over 5K+ rows of data. Thanks! Here's a condensed example:
Hi!

Try the formula below too in Coditional Formatting:

=SUM(--(MMULT(--($A2:$F2=$A$2:$F$10),TRANSPOSE(COLUMN($A1:$F1)/COLUMN($A1:$F1)))=COLUMNS($A1:$F1)))>1

Markmzz
 

Forum statistics

Threads
1,077,635
Messages
5,335,379
Members
399,014
Latest member
hamzalaarif

Some videos you may like

This Week's Hot Topics

Top