Problems with identifying duplicates

[chrisaabb]

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.

Thank you in advance for your help.

Kind regards


Chris

 

[Gates Is Antichrist]

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]

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]

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]

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