compare records with two sheets in vba

Topic

New Reply

Hi,

How can i compare records (means rows and column) with two worksheet to highlight duplicate records in those two worksheets?

Any help would be great!

Reply | Quote

Replies

Dear anasali94

I woud suggest using formulas rather than VBA.

Whether you use VBA or just formulas the key is to make sure there is a unique ID for Each Record so Excel can compare the lists.

By Example if COL A5 = Vin123 Col B5 = 5 and Col C5 = 2500
Excel cannot compare until you tell it what to look for in each list.
You could look for duplicates of just “VIN123” or Dupliates for just “2500” or Duplicates of “VIN123 5 2500” or duplicates of “VIN123 2500” So each list may need a formula to create the unique item you want to compare in each list.
The Excel Formula you will need is to check for duplicates is “ISNA(MATCH(D5,Range for other list unique ID,false))” going futher assusming the list unique ID is Sheet2!$D$5:$D$418 the formula becomes =ISNA(MATCH(D5,Sheet2!$D$5:$D$418,False)) then copy down as many rows as needed.

Again no matter what way you go Excel must have a unique ID.

Good Luck

Reply | Quote

Hey duthiet

Thank you, that really helps me!!

Reply | Quote

Hi anasali94

In addition to the advice from duthiet, you should be aware that from Excel 2007 onwards there is a ribbon command (in the [Data] group ) to ‘Remove Duplicates’.
If your goal is simply to create a list of unique records from two sheets, you could simply copy and append both lists to a new sheet and use the ‘Remove Duplicates’ function.

zeddy

Reply | Quote