Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Nov 2012
    Posts
    49
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Talking ~ merge two spreadsheets with one sheet each into a new one based on matching ids. please advise ~

    hey im trying to do hobby project and i have this really big problem: im trying to merge two spreadsheets with one sheet each into a new one. they should be merged based on matching ids in column A1:A1000, so the outcome would not have duplicated ids.

    im assuming this is a common need but i couldn't find anything for excel 2013

    please advise. link to a good video guide would be helpful and good.
    Last edited by computerbabyproblems; 2013-08-30 at 06:27.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach an example file to walk you through the steps?

    Steve

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    CBP,

    You could copy all the records from say sheet#2 below the records on sheet#1.
    Then google "Excel remove duplicates" w/o the quotes for several answers. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Lounger
    Join Date
    Nov 2012
    Posts
    49
    Thanks
    1
    Thanked 0 Times in 0 Posts
    *** THANKS SO MUCH!!

    sorry i could i not explain this any better :/

    i found what i needed -- http://www.youtube.com/watch?v=NYX3x32CeSg

    i needed to know what the word 'consolidate' in excel meant. this is by labels, so 'top row' and left column'

    this is for making a map!

  5. #5
    Lounger
    Join Date
    Nov 2012
    Posts
    49
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Talking

    sadly i need to request for aid again as i realised far too late that 'consolidate, from what i understand, only does numerical data...

    advise please?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach an example workbook, showing what you have and what you want?

    Steve

  7. #7
    Lounger
    Join Date
    Nov 2012
    Posts
    49
    Thanks
    1
    Thanked 0 Times in 0 Posts
    first sheet is just data from A-C1 : A-C1000

    the same as the sheet you want to merge with

    only difference is A2:A1000 would have similar ids/labels so the id would need to match upon merge and not be duplicated (the consolidate video shows how the outcome would be)

    the first post describes what the problem is though.

    everything is already there in the first 2 post for an advice to the problem

  8. #8
    Lounger
    Join Date
    Nov 2012
    Posts
    49
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i mean you're just merging two sheets that have different label for the columns

    the only column that has the same label is A1

    and A2:A1000 is what should be matched and not duplicated

  9. #9
    Lounger
    Join Date
    Nov 2012
    Posts
    49
    Thanks
    1
    Thanked 0 Times in 0 Posts
    you're just combing data based on the matching ids/label and duplicating them

  10. #10
    Lounger
    Join Date
    Nov 2012
    Posts
    49
    Thanks
    1
    Thanked 0 Times in 0 Posts
    it would be type B on http://welfarestate.com/Excel-Data-Matching-Merger/ but that script doesn't seem to work on excel 2013

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Did you follow the instructions that RetiredGeek posted at http://windowssecrets.com/forums/sho...l=1#post919609

    Steve

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I can not test in XL2013. You may need to discuss with the author of the software any issues you are having with it. The code is password protected so I will not comment or discuss any of the coding elements.

    Steve

  13. #13
    Lounger
    Join Date
    Nov 2012
    Posts
    49
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Talking

    Quote Originally Posted by sdckapr View Post
    Did you follow the instructions that RetiredGeek posted at http://windowssecrets.com/forums/sho...l=1#post919609

    Steve
    that is for a completely different problem though =)

    it's easier to solve this problem than
    to figure out what in the world that error with the script is (which would end not working anyhow)

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    that is for a completely different problem though
    I don't see how it is different. RetiredGeek indicated how you can merge and remove duplicates, which seems to be your questions

    to figure out what in the world that error with the script is
    But this is NOT a question for this board, but the author of the code. The author of the code has put protection on it, so it is up to him/her to change it or offer the code to us to allow us to see and modify it.

    Steve

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi computerbaby

    OK. I've checked out what the problem is.
    You can use the merge file tool you mentioned in post#10.
    The reason it doesn't work in Excel2013 is because it creates a new workbook for the merged data, and it assumes (incorrectly) that all new workbooks have 3 sheets in them, named Sheet1, Sheet2, Sheet3.
    The default setting in Excel 2013 is to create new workbooks with only one sheet in them.
    So, to use that merge tool, you need to first adjust you Excel2013 so that it creates 3 sheets in a new file, rather than 1.
    To do this:
    1. Start Excel2013 with a blank workbook.
    2. In the top-panel Ribbon, select File
    3. In the left-hand displayed pane, select Options
    4. Select General (left-hand pane)
    5. On the right-hand pane there is a section When creating new workbooks
    6. Change the dropdown value to 3 where is says Include this many sheets
    7. In the bottom-right corner, click the [OK] button

    Now load the merge tool file [Khalids_Merge_Data.xls]
    It should now work OK in Excel2013 (I tested it was OK with type B, and 2 files).

    Please let me know if this now does what you want.

    zeddy

  16. The Following User Says Thank You to zeddy For This Useful Post:

    computerbabyproblems (2013-09-05)

Page 1 of 2 12 LastLast

Posting Permissions

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