+ Reply to Thread
Results 1 to 6 of 6

Combine 2 sheets into 1 based on SKU

  1. #1
    Registered User
    Join Date
    01-25-2022
    Location
    Canada
    MS-Off Ver
    Online Office, Libre Office
    Posts
    3

    Combine 2 sheets into 1 based on SKU

    Hello Everyone,

    I have 2 spreadsheets that I need to combine into one. They have multiple different columns but have one column (SKU) in common. The challenge is that one file (call it A) has one of each SKU, but the second (B) has up to 6 rows of the same SKU.

    How can I combine these 2 documents so that the SKU's all line-up?

    Thanks,

    John
    Last edited by FDibbins; 01-26-2022 at 12:14 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,901

    Re: Spreadsheet Questions

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

    (I have changed the title for you - this time )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-25-2022
    Location
    Canada
    MS-Off Ver
    Online Office, Libre Office
    Posts
    3

    Re: Combine 2 sheets into 1 based on SKU

    Thank you very much for the feedback Ford. I hope that I am communicating what I need done in a clear way, my concern is that I am not using the correct terminology. Should I take the below comment and add it to the original post? I welcome any tips or advice you and the community can provide.

    Basically on the second screenshot I need to merge E2-E7 into one cell with the contents divided by "//". Then I need E8-E10 to also merge and be separated by "//". This is so that each SKU will be contained in one row instead of several and I can combine it with the spreadsheet in screenshot one.

    I need rows 3-7 and 9-10 to completely delete after the above merge is complete.

    Attachment 765773
    Attachment 765774

    Thanks Ford,

    John

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,901

    Re: Combine 2 sheets into 1 based on SKU

    Instead of providing screen shots (which we cannot really work with), please upload a CLEAN small sample WB, showing examples of what you have, what you want, and how you get that.

  5. #5
    Registered User
    Join Date
    01-25-2022
    Location
    Canada
    MS-Off Ver
    Online Office, Libre Office
    Posts
    3

    Re: Combine 2 sheets into 1 based on SKU

    Hi FDibbins (and anyone else that can help).

    The workbook is linked. I got from the first sheet to the third sheet by manually copying and pasting the URL's, typing the //'s, and deleting the now redundant rows. Given that the workbook has over 80,000 lines of data, doing this work manually is not practical.

    Thanks,

    JC
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,063

    Re: Combine 2 sheets into 1 based on SKU

    Not sure how this would work with either the online version or with libre office, however perhaps it will be of some help:
    1. Populate F2 and down using: =IF(D2=1,TEXTJOIN(" // ",TRUE,E2:INDEX(E2:E$15,AGGREGATE(14,6,(ROW(E2:E$15)-ROW(E1))/(A2:A$15=A2),1))),"")
    2. Populate H2 across and down using: =IFERROR(INDEX(A$2:A$14,AGGREGATE(15,6,(ROW(A$2:A$14)-ROW(A$1))/($D$2:$D$14=1),ROWS(H$2:H2))),"")
    Note that for the Picture column the index references column F.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. A daily sales spreadsheet few questions
    By Cor4eyh in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2014, 02:09 PM
  2. Some questions for my League of Legends Statistics spreadsheet
    By mega_nerd in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-16-2013, 09:07 AM
  3. [SOLVED] Questions opening a spreadsheet
    By Gandalf2524 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2013, 04:21 PM
  4. 2 questions: hyperlinking all URLs in a spreadsheet and autofilling sentences
    By TheVolkinator in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-14-2013, 09:58 AM
  5. questions that aren't really excel questions
    By martindwilson in forum The Water Cooler
    Replies: 1
    Last Post: 03-11-2012, 08:21 PM
  6. Training spreadsheet questions
    By Maisiedays in forum Excel General
    Replies: 5
    Last Post: 05-14-2010, 12:40 PM
  7. Spreadsheet Questions
    By Metalteck in forum Excel General
    Replies: 4
    Last Post: 06-02-2005, 03:05 PM

Tags for this Thread

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