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