Find matching data between 2 excel spreadsheets RRS feed

  • Question

  • I have two separate spreadsheets with similar data columns that I want to compare and find matches and write the information to a new sheet, I would need to compare multiple columns (for match by name and address for example). Preferably I would like to do this through an vba excel macro. I remember reading somewhere that excel sheets were actually tables similar to SQL. So is there a way to compare them using sql syntax (i.e inner join using column names etc). Any help would be appreciated.


    Wednesday, July 15, 2020 7:14 PM

All replies

  • Excel sheets are not tables similar to SQL; they are tables similar to relational tables. Excel lacks most of what SQL does.

    One solution is to read one of the spreadsheets and create a Dictionary object from the data. Then read the other spreadsheet and use the Exists method to determine if it exists in the first spreadsheet.

    Sam Hobbs

    Wednesday, July 15, 2020 7:53 PM
  • Re:  comparing two worksheets

    The free Professional_Compare workbook may be helpful.
    (it uses two dictionaries)
    Image shows result with identical cells (on separate worksheets) shaded green.
    To use, go to the second worksheet (Clean Data+) and use the "List Common Items" button.

    Download from MediaFire (no ads)...

    • Edited by Nothing Left to Lose Thursday, July 16, 2020 2:21 AM added directions on which button to use
    Wednesday, July 15, 2020 10:56 PM
  • Using the inner join in SQL is the same as creating a concatenated field to match with. In the example you have if you creat a new column of name & city & zip - then use A Vlookup you’re are essential doing the same as you wanted to do- only difference is the contents of the inner joint is an actual new field. HTH David
    Thursday, July 16, 2020 2:36 PM
  • Excel 365 Pro Plus with PowerPivot and Power Query.
    With PQ Fuzzy Left Outer Join.
    No formulas, no VBA macro.
    With Nothing's data base.

    Friday, July 17, 2020 2:51 AM