You are here:

Excel/Showing Duplicate values

Advertisement


Question
QUESTION: Hello Tom,
Hope you are doing well.

I would like to know is there any formula or macro which will show me the duplicate records in red or any color in row.

I am aware of function "remove duplicate" in excel 2007 and above version
But i  want to see duplicate records in highlighted format first.

e.g (in below table , row no 1 and 5 , 6 and 8 are duplicate)
i want the system to show all the records in any color.

ORI   DEST   FB   RTNG   AMT

BOM   DUS   KHRTAU   4521   523
BOM   LON   KHRTAU   4521   523
BOM   MCT   KHRTAU   4521   523
BOM   DUS   KHRTAU   4521   523
DEL   PAR   KHRTAU   4521   523
DEL   KHI   KHRTAU   4521   847
DEL   PAR   KHRTAU   4521   523
DEL   LHE   KHRTAU   4521   847



Thanks
Rakesh

ANSWER: Rakesh,

In a column F you could do this

Say you data starts in row 1, then in

F1:  =if(countif(A:A,A1,B:B,B1,C:C,C1,D:D,D1,E:E,E1)>1,"Dup","")

then drag fill that down the column.

Now you could use that column to set up conditional formatting to highlight the row.

If you need an example, send the data above or similar data to

twogilvy@msn.com

note that the multiple condition IF statement requires excel 2007 or later.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hello Tom,
Thanks its working, but just for sample i have provided you small data,
My column and rows will go upto thousand when actually working.
So can u pls help me with macro, which will show me records in highlighted forms?

Not that much urgent. reply me whenever you want in this week

ANSWER: Rakesh

This assumes you have headers in row 1 and your data values start in row 2.
I marks all rows that have duplicates with a red background.  
A duplicate would be found if I copied a row and pasted it on top of another row.  So it checks that the values match for each cell in both rows.  

Sub MarkDupes()
Dim col As Long, i As Long, bDup As Boolean
Dim r As Range, cell As Range, cell1 As Range
col = Cells(2, Columns.Count).End(xlToLeft).Column
Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))
For Each cell In r
 For Each cell1 In r
   If cell1.Address <> cell.Address Then
     bDup = True
     For i = 1 To col
       If Cells(cell.Row, i) <> Cells(cell1.Row, i) Then
         bDup = False
         Exit For
       End If
     Next
     If bDup = True Then
         cell.Resize(1, col).Interior.ColorIndex = 3
     End If
   End If
 Next
Next
End Sub

lightly tested - but worked for me.

--
regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: Hello Tom,
Hope you are fine.

You helped me a lot , thanks for that
I need a small help again on the below macro you provided.

Sub MarkDupes()
Dim col As Long, i As Long, bDup As Boolean
Dim r As Range, cell As Range, cell1 As Range
col = Cells(2, Columns.Count).End(xlToLeft).Column
Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))
For Each cell In r
For Each cell1 In r
  If cell1.Address <> cell.Address Then
    bDup = True
    For i = 1 To col
      If Cells(cell.Row, i) <> Cells(cell1.Row, i) Then
        bDup = False
        Exit For
      End If
    Next
    If bDup = True Then
        cell.Resize(1, col).Interior.ColorIndex = 3
    End If
  End If
Next
Next
End Sub

With the help of the this macro i get the duplicate data with red color.
i want that , one set should be highlighted with red color and another set with blue color or any other color so that i can remove single set with the help of filter function in excel 2007.

Hope you got what i want to say
(e.g as below
if below is the list which i want to see duplicate

BOM
DEL
MAA
BOM
TRV
LAX

then first BOM should be highlited in red color and another BOM is with blue color.

Thanks
Rakesh

Answer
Rakesh,

when there are duplicates, this should mark the first row as red and any duplicates beyond that as blue.

Sub MarkDupes()
Dim col As Long, i As Long, bDup As Boolean
Dim minrow As Long
Dim r As Range, cell As Range, cell1 As Range
col = Cells(2, Columns.Count).End(xlToLeft).Column
Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))
For Each cell In r
minrow = r(r.Count).Row + 10
For Each cell1 In r
  If cell1.Address <> cell.Address Then
    bDup = True
    For i = 1 To col
      If Cells(cell.Row, i) <> Cells(cell1.Row, i) Then
        bDup = False
        Exit For
      End If
    Next
    If bDup = True Then
      If cell1.Row < minrow Then minrow = cell1.Row
      If cell.Row <= minrow Then
        cell.Resize(1, col).Interior.ColorIndex = 3
      Else
        cell.Resize(1, col).Interior.ColorIndex = 5
      End If
    End If
  End If
Next
Next
End Sub

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.