Macro to check for duplicates and highlight duplicates

Hi there,

I have a worksheet which record the hours spent on each job for each employee. I have a macro which will import each employee time entry. When i import the data to the worksheet, i want to check if there is any duplicates data in case i import the same file twice. I have try to use conditional formatting before but i doesn't seem to be working. I have to check against 5 columns of data to check for the duplicates and conditional formatting seems like can only works for a single column.

Can someone please show me how to write a macro for this?

My data have total of 6 columns, start from column A to H, and the data entry starts from row 2. I have to check against column A,B,C,E&G for duplicates. I also want to highlight the duplicates instead of directly delete the row in case there is any mistake.

Thanks!!

Hi,

The approach I usually take to tasks like this is to first create a helper column, and in this column add the formula =A1&B1&C1&E1&G1, then copy this formula down your data rows. Then in another helper column, and assuming your first helper column is say H, add the following formula in the row 1 cell of the second helper column
=COUNTIF(H:H,H1)
and copy this down your data.

If there are duplicates this will result in an integer >1. Then all you need do is filter your data using this column for values >1 to identify the duplicates.

HTH

i use conditional formatting

i hope this will help

kindly find attached

Originally Posted by melleniam

i use conditional formatting

i hope this will help

kindly find attached

Hi Melleniam,

conditional formatting only works for one column, i have to compare data for more few columns. I'll have to add a helper column to use the formula from HTH, then only apply conditional formatting on the helper column.