none
Join Two Excel Sheets That Have a One to Many Relationship RRS feed

  • Question

  • I have 2 sheets, and each has a common column. Currently the linking column name is different in the 2 sheets, so I don't know if the column names must be the same or not. Let's call the Parent-Sheet and Child-Sheet.

    Parent-Sheet has all unique values as required.

    Child-Sheet has 4 fields in total making each row unique. The primary key is a composite of the foreign key for Parent-Sheet and a foreign key from another table. Let's call this AccountParentSheet. Child-Sheet in this case is the junction table in a many to many relationship. But I do not need to get data from AccountParentSheet.

    I want to merge the two sheets into a single sheet called ParentsAndChildrenSheet. Each row in Parent-Sheet that has a matching key in Child-Sheet should have a complete row with all the values from both source sheets. Some of the Parent-Sheet rows will necessarily be duplicated to provide a place for the related Child-Sheet row. If it is possible or easier, it is okay to merge the sheets in place (on the Parent-Sheet).

    Is there a built in function in Excel to do this or must I write a VBA function to manually build the merged sheet?

    If you are curious, the two sheets are extracted Views from a custom MS Dynamics 365 solution.

    Thanks in advance.


    J. Mike

    Friday, November 17, 2017 2:56 PM

All replies

  • Hi,

    I can understand half, and cannot understand half. I suppose you must write VBA code.
    Please insert a screenshot of Parent-sheet, Child-sheet and ParentsAndChildrenSheet (you want) as a simple sample, or share them via cloud storage such as OneDrive, Dropbox and so on.

    Regards,

    Ashidacchi

    Saturday, November 18, 2017 12:21 AM
  • Hello,

    I think you have to write macro to merge the sheet. It may be tedious to read/write date use Excel Object Model. You could use ADO to read/write the data.

    Please visit Using ADO to Query an Excel Spreadsheet

    ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks

    If you have any problem, i suggest you share the screenshot or sample file here.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 24, 2017 2:59 AM
    Moderator