Mark duplicate records with a color

cindygo · Dec 7, 2010

I’m using the advanced feature to find duplicate records however I don’t want to delete them but need to color the rowS for all duplicate records.

The columns in the xls range A:AQ
The client information which contain possible dup date range A:F

In one example there are 19 records. There are two duplicate records in the following rows 11-12 and 16-17 are. I would like code that finds these and colors all text in those rows Red.

Is this possible? Thanks,
Cindy

Ruddles · Dec 7, 2010

Conditional Formatting > Highlight Cells Rules > Duplicate Values...

cindygo · Dec 7, 2010

Thanks Ruddles but Conditional Formatting isn’t giving me what I need. Here is more information.

Columns A:F are First Name, Last Name Address, City, State, Zip
Conditional Formatting is highlighting duplicates within each column. In the example below all records are in the same City and State therefore all cells in the City/State columns will be highlighted using CFormatting.

I need to find duplicate records that have the same unique First Name, Last Name Address, City, State, Zip within a row, much like the Advanced feature. The issue I am having with Advanced feature is that it filters the records that are duplicates and doesn’t allow me to color code them
<table class="MsoNormalTable" style="width: 261.75pt; margin-left: 4.65pt; border-collapse: collapse;" width="349" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15pt;"> <td style="width: 50.25pt; background: none repeat scroll 0% 0% rgb(196, 189, 151); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="67" nowrap="nowrap"> FirstName
</td> <td style="width: 47.25pt; background: none repeat scroll 0% 0% rgb(196, 189, 151); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="63" nowrap="nowrap"> LastName
</td> <td style="width: 42.75pt; background: none repeat scroll 0% 0% rgb(196, 189, 151); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="57" nowrap="nowrap"> Address
</td> <td style="width: 0.5in; background: none repeat scroll 0% 0% rgb(196, 189, 151); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="48" nowrap="nowrap"> City
</td> <td style="width: 40.5pt; background: none repeat scroll 0% 0% rgb(196, 189, 151); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="54" nowrap="nowrap"> State
</td> <td style="width: 45pt; background: none repeat scroll 0% 0% rgb(196, 189, 151); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="60" nowrap="nowrap"> ZipCode
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN1
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN1
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add1
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip1
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN2
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN2
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add2
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip11
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN3
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN3
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add3
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip1
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN3
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN3
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add3
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip1
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN5
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN5
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add5
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip22
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN1
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN7
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add6
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip4
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN2
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN7
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add7
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip4
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN8
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN8
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add8
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip50
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN9
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN9
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add9
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip60
</td> </tr> <tr style="height: 9pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 9pt;" valign="bottom" width="67" nowrap="nowrap"> FN10
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 9pt;" valign="bottom" width="63" nowrap="nowrap"> LN10
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 9pt;" valign="bottom" width="57" nowrap="nowrap"> Add10
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 9pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 9pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 9pt;" valign="bottom" width="60" nowrap="nowrap"> Zip1
</td> </tr> <tr style="height: 12.15pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 12.15pt;" valign="bottom" width="67" nowrap="nowrap"> FN10
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 12.15pt;" valign="bottom" width="63" nowrap="nowrap"> LN10
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.15pt;" valign="bottom" width="57" nowrap="nowrap"> Add10
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 12.15pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 12.15pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 12.15pt;" valign="bottom" width="60" nowrap="nowrap"> Zip1
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN12
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN12
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add12
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip100
</td> </tr> <tr style="height: 3.5pt;"> <td style="width: 50.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="67" nowrap="nowrap"> FN1
</td> <td style="width: 47.25pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="63" nowrap="nowrap"> LN13
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="57" nowrap="nowrap"> Add13
</td> <td style="width: 0.5in; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="48" nowrap="nowrap"> City1
</td> <td style="width: 40.5pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="54" nowrap="nowrap"> State1
</td> <td style="width: 45pt; padding: 0in 5.4pt; height: 3.5pt;" valign="bottom" width="60" nowrap="nowrap"> Zip11
</td> </tr> </tbody></table>

hiker95 · Dec 7, 2010

cindygo,

With your raw data already sorted/grouped:

Excel Workbook

A

B

C

D

E

F

1

FirstName

LastName

Address

City

State

ZipCode

2

FN1

LN1

Add1

City1

State1

Zip1

3

FN2

LN2

Add2

City1

State1

Zip11

4

FN3

LN3

Add3

City1

State1

Zip1

5

FN3

LN3

Add3

City1

State1

Zip1

6

FN5

LN5

Add5

City1

State1

Zip22

7

FN1

LN7

Add6

City1

State1

Zip4

8

FN2

LN7

Add7

City1

State1

Zip4

9

FN8

LN8

Add8

City1

State1

Zip50

10

FN9

LN9

Add9

City1

State1

Zip60

11

FN10

LN10

Add10

City1

State1

Zip1

12

FN10

LN10

Add10

City1

State1

Zip1

13

FN12

LN12

Add12

City1

State1

Zip100

14

FN1

LN13

Add13

City1

State1

Zip11

15

Sheet1

After the macro using column G as a helper column:

Excel Workbook

A

B

C

D

E

F

1

FirstName

LastName

Address

City

State

ZipCode

2

FN1

LN1

Add1

City1

State1

Zip1

3

FN2

LN2

Add2

City1

State1

Zip11

4

FN3

LN3

Add3

City1

State1

Zip1

5

FN3

LN3

Add3

City1

State1

Zip1

6

FN5

LN5

Add5

City1

State1

Zip22

7

FN1

LN7

Add6

City1

State1

Zip4

8

FN2

LN7

Add7

City1

State1

Zip4

9

FN8

LN8

Add8

City1

State1

Zip50

10

FN9

LN9

Add9

City1

State1

Zip60

11

FN10

LN10

Add10

City1

State1

Zip1

12

FN10

LN10

Add10

City1

State1

Zip1

13

FN12

LN12

Add12

City1

State1

Zip100

14

FN1

LN13

Add13

City1

State1

Zip11

15

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Sub DupesRedText()
' hiker95, 12/07/2010, ME513839
Dim LR As Long, a As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("G2").Formula = "=A2&B2&C2&D2&E2&F2"
Range("G2").AutoFill Destination:=Range("G2:G" & LR)
For a = 2 To LR Step 1
  If Range("G" & a).Value = Range("G" & a + 1) Then Range("A" & a & ":F" & a + 1).Font.ColorIndex = 3
Next a
Range("G2:G" & LR).ClearContents
Application.ScreenUpdating = True
End Sub

Then run the DupesRedText macro.

cindygo · Dec 8, 2010

hiker95 - that works perfectly! Thank You!

hiker95 · Dec 8, 2010

cindygo,

You are very welcome.

Glad I could help.

Come back anytime.

Mark duplicate records with a color

cindygo

New Member

Excel Facts

Ruddles

Well-known Member

cindygo

New Member

hiker95

Well-known Member

cindygo

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Mark duplicate records with a color

cindygo

New Member

Excel Facts

Ruddles

Well-known Member

cindygo

New Member

hiker95

Well-known Member

cindygo

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock