locked
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.

    Thanks.

    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
    SimpleSamples.Info

    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)...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    • 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.
    http://www.mediafire.com/file/lrqs3qeguuea9sr/07_16_20a.xlsx/file
    http://www.mediafire.com/file/s3ju484rcx7wjuz/07_16_20a.pdf/file

    Friday, July 17, 2020 2:51 AM