# Problems with identifying duplicates

#### chrisaabb

##### New Member
Hi I have a large spreadsheet with many rows and columns of data but I need to be able to identify duplicates across 3 columns.

Column A has an ID Number
Column B has a Staff Number

First stage is to go vertically through column A and then column B and highlight any rows with duplicates in either column A and/or column B in yellow.

This is then repeated across these columns horizontally.

Once these duplicates have been identified I then need all the rows highlighted in yellow to be removed from the original sheet and placed in a new worksheet.

I then need a third check in the new worksheet against Area which is stored in Column D. If the person is shown as being in the same area more than once, then all rows will be coloured in red. If the person is shown as being in different areas, then the rows will remain in yellow.

The final stage is to transfer all records in the new worksheet which are coloured yellow back to the original sheet. For those records in red, all of them remain in the new worksheet with the exception of one record which is transferred back to the original sheet.

I understand that I can achieve this using VLOOKUP and HLOOKUP with conditional formatting but although I can create most of the individual steps, I do not have the skills to put this all in to a macro.

If you need a sample of the workbook with some dummy data, please let me know.

Kind regards

Chris

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Gates Is Antichrist

##### Well-known Member
My mental stack hit capacity on the first couple of stages.

3 column comparison is stiffer than 2 columns. So add two conditional formats to column A. The first checks against B by setting
Cell Value, Equal to, =B1
and
Cell Value, Equal to, =C1
for A1.
In B change it to check A and C, and in C check A and B.

That's the first step. Now for the row formatting, the obvious answer is in VBA. Since I don't assume people know that, and since I avoid VBA when an intrinsic solution is practical, I'll suggest the heresy of adding an additional column like
E1=if(A1=B1,"dupe",if(A1=C1,"dupe",if(B1=C1,"dupe","")))
and use a third conditional format based on \$E1. Make that the sole conditional format for D1:IV1.

So far so good? Meanwhile before you know it someone will devise a compound condition that avoids the extra column

#### Gates Is Antichrist

##### Well-known Member
I told you my mental stack was full. It ran over, as I now look back and see you don't just want to get duplicates in the same row, but in any row.

No problem. Change
=B1
to
(EDIT, I wrote it wrong earlier
=NOT((ISERROR(MATCH(A1,B:B,FALSE))))
as a "formula" conditional format condition. and copy for column A, and do B and C analogously. Then build column E analogously.

MATCH is a little "cheaper" than VLOOKUP so use it when feasible.

#### Boller

##### Banned
I understand that I can achieve this using VLOOKUP and HLOOKUP with conditional formatting but although I can create most of the individual steps, I do not have the skills to put this all in to a macro.

Use the macro recorder to create a macro from the steps you take to achieve what you need.

From a quick reading of your post, sounds like use of AdvancedFilter/UniqueItemsOnly might be of some use.

#### chrisaabb

##### New Member
Thank you for your quick responses. I have tried to add the conditional formatting to column A as described with no success. I agree that a macro recorder is probably the best option once I have got all the steps worked out but I need to get this right first. I can attach a small sample of dummy data but as I am new to the forum I don't know how to do this - please advise.

Thanks again - much appreciated.

Chris

Replies
2
Views
159
Replies
1
Views
495
Replies
2
Views
358
Replies
6
Views
235
Replies
26
Views
557

1,191,671
Messages
5,987,956
Members
440,121
Latest member
eravella

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

### Which adblocker are you using?

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

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