Saturday, February 12, 2011

yogi_MergingDataFromDifferentSpreadsheets


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com




MyList

CompanyList
Nathanael Emenaker said ...
"I want to be able to merge both of these lists and remove the duplicate names, so that the all my information remains, but the new names, the customers I have not contacted, are integrated into my list"
---------------------------------------------
in the solution presented in the following spreadsheet, I have used some intermediary calcs shown in gray colored text in the columns to the right

6 comments:

  1. I have to do this exact thing. Can you explain what each of the calcs is doing to merge the non-duplicate data? I'm not understanding how it works.

    Thanks!

    ReplyDelete
  2. Hi MadDog:

    My solution uses Google Apps Script VMERGE (written by ahab) to consolidate common fields from MyList spreadsheet and CompanyList spreadsheet. To enable use of VMERGE, one has to first insert this script in the solution spreadsheet.

    Then I use the QUERY function and the FILTER functions to obtain the desired result.

    My suggestion would be for you to first use the data in my Blog post to see if you can recreate the solution in my Blog post. If you need I will be glad to share with you the spreadsheet from which this Blog post was created.

    Once you are able to recreate the result from the sample data in my Blog post, then you should be able to apply the solution to your own data.

    Good Luck!

    ReplyDelete
  3. Hi Yogia,

    Could you possibly share this spreadsheet with me? I'm pretty sure this is exactly what I'm looking for.

    ReplyDelete
  4. Hi Ian:

    The link to the associated spreadsheet is:
    https://spreadsheets.google.com/spreadsheet/ccc?key=0AkHBcyclu11AdGJvY1Z4cFRHMkNVUjNKcjVGSzRtc2c&hl=en_US&authkey=CJfaiL0E

    Cheers!
    Yogi

    ReplyDelete
  5. You Are Very Welcome Ian ... Now Let Us Keep Googling.

    Cheers!
    Yogi

    ReplyDelete