You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Showing Duplicate values

Advertisement

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

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

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | you are great!!! |

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

Answers by Expert:

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.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.