+ Reply to Thread
Results 1 to 5 of 5

Merge dissimilar data to different columns based on common unique ID / insert lines

  1. #1
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Merge dissimilar data to different columns based on common unique ID / insert lines

    Hi all,

    This is a bit of a tricky one. I'm attempting to wrangle my source data in to an xml format, and have in to this issue.

    I have source data in two sheets. Both sheets have items that are linked by using a uniqueID. I would like to loop through the data in one sheet, find the last row for each unique ID, and then pull in the data from the other sheet with the same uniqueID and insert it immediately below the unique ID's from the first sheet. Loop and continue until all data has been paired/inserted on to the one sheet. On copying across new data I also need to copy down the preceeding row for some columns (in order to maintain the xml structure)

    I've attached a sample sheet.
    Sheet: XMLAttempt: Part 1 of the data
    Sheet: SplitMethod: Needs to be paired with XMLAttempt data based on Column A UniqueID
    Sheet: XMLAttempt_Result: This is what I hope the final result to look like. I have highlighted the data copied from SplitMethod as to where it should be inserted.

    I know this is probably a little larger than the average help request. I'm sorry. A few snippets this forum has helped me work out in the past few weeks have really helped my skills along, but I'm not sure how to go about this one.

    XMLAttempt Data
    Merge1.JPG



    SplitMethod Data
    Merge2.JPG



    Result
    merge3.JPG
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Merge dissimilar data to different columns based on common unique ID / insert lines

    bump back to visible after a few days of no activity

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Merge dissimilar data to different columns based on common unique ID / insert lines

    Hi anakaine,

    I took your sample workbook above and made the data on the Attempt and Method sheets a table first. Then I used "Get & Transform" (on the Data tab of 2016) to pull in both the previous (now) tables of data. I needed to change the "Unique" to "Unique ID" so the column heads were the same on both tables. Then using Power Query (on the Data Tab again) I did a simple Append query and sorted by the first column.

    The above produced sheet1 for the Attempt table, Sheet2 for the Methods table and Sheet3 your answer, never needing VBA or any formulas. Time to learn and use Power Query?

    PQ Append Maybe.xlsx

    Watch https://www.youtube.com/watch?v=LSDQGWdgNJs
    and note you have 2016 so all of Power Query is on the Data -> New Query. I created two new sheets instead of only a "connection" on the Load and Close process.
    Last edited by MarvinP; 09-24-2016 at 06:57 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Merge dissimilar data to different columns based on common unique ID / insert lines

    Thanks MarvinP, I'll give it a shot on my own and see if I can replicate
    Last edited by anakaine; 09-24-2016 at 07:05 PM.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Merge dissimilar data to different columns based on common unique ID / insert lines

    Different method
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Spliiting 1 row of data into 2 lines based on next unique value
    By michelle 1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2016, 02:59 PM
  2. [SOLVED] Merge Multiple Columns of Different Lengths With Common Values
    By jcolarusso in forum Excel General
    Replies: 3
    Last Post: 07-28-2015, 10:44 AM
  3. Merge worksheets with unique key in common
    By porpitax2 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-22-2015, 11:00 AM
  4. [SOLVED] merge duplicate lines while appending unique data
    By tiger10012 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-26-2013, 07:41 AM
  5. How to merge duplicates while concatenating unique data from 3 columns
    By mdhillyer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-11-2012, 01:13 AM
  6. Replies: 11
    Last Post: 09-02-2011, 11:17 AM
  7. [SOLVED] Compare multiple column of data and list out common and unique component in adj columns
    By kuansheng in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-01-2006, 06:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1