Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    compare records with two sheets in vba

    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!

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    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

  3. #3
    New Lounger
    Join Date
    Feb 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey duthiet

    Thank you, that really helps me!!

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

Tags for this Thread

Posting Permissions

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