Compare two columns and return list of duplicates

**asha3010** · 06-10-2010, 09:39 AM

Greetings,

I have a file containing two worksheets. Sheet1 contains data (line details for every contract number) that I download from a website. This data is then updated via macro to Sheet2, which is basically the database for all data that has been downloaded.

In order to avoid duplicate update of the database, I would like to incorporate some code in the existing macro that will do the following:

1. Check the contract number in Sheet1 to see if this number already exists in Sheet2
2. Display via a message box / any other dialog box the duplicate contract numbers and then exit the macro.

If it helps, I have attached a sample file.

Many thanks in advance
Asha

**jaslake** · 06-10-2010, 01:39 PM

Hi asha3010

What's the key that makes an item a duplicate? Columns C and D of each sheet? Line 1 of sheet 1 appears to be a duplicate but the Amt is different from sheet 2 Amt.

John

**asha3010** · 06-11-2010, 03:08 AM

Hello jaslake,

Thanks for your response.

What's the key that makes an item a duplicate? Columns C and D of each sheet?

Col D of each sheet contains the unique key. For eg, the code should not allow me to proceed with the update since Row2 in Sheet1 has a contract number that already exists in Sheet2.

Line 1 of sheet 1 appears to be a duplicate but the Amt is different from sheet 2 Amt.

You are right. That was a typo - sorry about it.

Hope this clarifies.

Many thanks again
Asha

**jaslake** · 06-11-2010, 10:35 AM

Hi asha3010

I'm confused. Worksheet 2, Col D has duplicates yet you've said

Col D of each sheet contains the unique key

Based on my understanding of your requirements, line 2 of Worksheet 1 would be rejected and line 4 of Worksheet 1 would be rejected; line 3 of Worksheet 1 would be accepted. Is this correct?

John

**asha3010** · 06-11-2010, 11:00 AM

Hi John,

Let me elaborate. Sheet1 contains data that is downloaded from a website. Once this data is updated (via macro) to Sheet2, the same macro clears all data in Sheet1.

What I wanted to prevent is me accidentally downloading again the same data into Sheet1 without realising that it has already been updated to Sheet2. Hence, I wanted to check if the Contract Number in Sheet1 already exists in Sheet2.

So row2 in Sheet1 should be rejected but rows 3-4 should be accepted.

Hope this clarifies or is it as clear as mud?

Thanks so much for your patience.
Asha

**jaslake** · 06-11-2010, 11:03 AM

Hi asha3010

That helps. I'll look into it for you. I'll be traveling today.

John

**asha3010** · 06-11-2010, 01:19 PM

Thanks John. Safe travels!

**jaslake** · 06-12-2010, 12:34 AM

Hi asha3010

Try this code

Please Login or Register  to view this content.

The code first finds duplicate contract numbers and flags them. It then adds all unflagged contract numbers to sheet 2 and presents a message of duplicate contracts. Let me know of issues.

John

**asha3010** · 06-15-2010, 06:44 AM

Hello John,

Many thanks for your prompt response. Sorry for not checking the code earlier.

Your code works great. I tweaked it a bit - used the countif function since the original worksheet is protected & will not allow for any column inserts.

I have attached the revised file if you would like to see the countif code.

Many thanks again for your support.

Regards
Asha