+ Reply to Thread
Results 1 to 5 of 5

Macro to check for duplicates and highlight duplicates

  1. #1
    Registered User
    Join Date
    12-04-2008
    Location
    Penang, Malaysia
    Posts
    23

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

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    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

  3. #3
    Registered User
    Join Date
    05-23-2007
    Posts
    41
    i use conditional formatting

    i hope this will help

    kindly find attached
    Attached Images Attached Images

  4. #4
    Registered User
    Join Date
    12-04-2008
    Location
    Penang, Malaysia
    Posts
    23
    Quote Originally Posted by Richard Buttrey View Post
    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
    Hi Richard,

    Thanks!!

    Is there a way to convert this to macro?
    Last edited by obc1126; 12-30-2008 at 02:15 AM.

  5. #5
    Registered User
    Join Date
    12-04-2008
    Location
    Penang, Malaysia
    Posts
    23
    Quote Originally Posted by melleniam View Post
    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1