+ Reply to Thread
Results 1 to 22 of 22

combine two sheets into one

  1. #1
    Registered User
    Join Date
    06-15-2015
    Location
    usa
    MS-Off Ver
    2007
    Posts
    12

    Unhappy combine two sheets into one

    Hi guys I am struggling in combining two worksheets into one
    I receive one worksheet with these columns: Invoice# /Amount /Date, (the hard copy of the invoice contains the employee name but is not included in the first worksheet),
    then I received another worksheet with the Employee Name and Code #. (some employees use the same code#)

    I want to create one final sheet with the Code# / Amount.

    any suggestion will be greatly appreciated.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: combine two sheets into one

    Hi, welcome to the forum

    what is the link between the 2 sheet?

    I see 1 sheet has Invoice# and date
    the other 1 has EE name and Code#

    to combine them, you surely need a common field?
    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
    06-15-2015
    Location
    usa
    MS-Off Ver
    2007
    Posts
    12

    Re: combine two sheets into one

    there is no link at this point
    how can I create one?
    and after creating a common field what formula or function can I use to accomplish my job?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: combine two sheets into one

    you cant just "create" a common link. The link needs to be something that both sheets share, something that is the same/common.

    Like for instance, they both have an employee name/number, or they both have the same invoice number - something like that.

    If you did this manually, how would you do it?

  5. #5
    Registered User
    Join Date
    06-15-2015
    Location
    usa
    MS-Off Ver
    2007
    Posts
    12

    Re: combine two sheets into one

    I don't do the worksheets they are prepare by someone else I am just trying to combine them, the previous person in charge was creating the final worksheet manually but it takes too much time. (I was wondering if I can add another column with the employee initial or the complete name to have cell link, if this is possible what do I need to do next?)

    the worsheets look like this (in the final sheet I only need Code# and Amount)
    Workshhet 1 Worksheet 2
    invoce Amount Invoice Date Employee Name Code #
    1308 78.00 4/5/15 Employee 1 106-30-300
    1308 67.00 4/6/15 Employee 2 205-12-120
    1336 25.00 4/7/15 Employee 3 106-30-300
    1333 15.00 5/20/15 Employee 4 304-13-130
    1299 55.00 5/21/15 Employee 5 407-50-500
    1327 43.00 5/22/15 Employee 6 205-12-120
    1268 25.00 5/23/15 Employee 7 510-10-110

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: combine two sheets into one

    It seems that each row on both the sheets belong to the same information i.e. row1 is column header on both the sheets, row2 on both the sheets belongs to the record1, row3 belongs to record2....and so on. Is it so?

    If so, you can just copy the information from the sheet1 and paste on next empty column on sheet2 and delete all the unwanted columns so you are left with Code# and Amount on sheet2.
    Can't it be done like this?
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  7. #7
    Registered User
    Join Date
    06-15-2015
    Location
    usa
    MS-Off Ver
    2007
    Posts
    12

    Re: combine two sheets into one

    last month I created a column with the EE name A1, and in A2 the Code# then below A3-A15 add the invoice amounts one by one then autosum, I did this for every employee and every invoice, now I am trying to find a better faster way to do it, but we don't have too much experience with excel.

  8. #8
    Registered User
    Join Date
    06-15-2015
    Location
    usa
    MS-Off Ver
    2007
    Posts
    12

    Re: combine two sheets into one

    for some employees we use the same code#
    sheet 1 only has the invoice# and amount and date (I don't need date) -
    sheet 2 only has the EE name and code#

    If I add another column with the EE name to sheet 1 what would I need to do in order to get what I need?

    my goal is to create final sheet with code# and amount only
    I have try the IF but I could not nest all of the names

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: combine two sheets into one

    Show us 1 workbook with a small example of the sheets, without confidential information.

    To Attach a File:

    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  10. #10
    Registered User
    Join Date
    06-15-2015
    Location
    usa
    MS-Off Ver
    2007
    Posts
    12

    Re: combine two sheets into one

    worksheet attached
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: combine two sheets into one

    Indeed, there are no same/common cells.

    You need to create a same/common cell (as already offered).

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: combine two sheets into one

    How do you know which cells on worksheet 2 relate to which cells on worksheet 1? If they're simply the same rows, can't you just copy and paste?

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: combine two sheets into one

    Thats pretty much what I said, right from the start

    As I asked in post #4...
    If you did this manually, how would you do it?

  14. #14
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: combine two sheets into one

    Ideedy, doesn't seem to help though, my query or yours

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: combine two sheets into one

    Here is an example of combining 2 tables (you want sheets, they have tables - same principle)
    http://www.excelforum.com/excel-gene...on-column.html

  16. #16
    Registered User
    Join Date
    06-15-2015
    Location
    usa
    MS-Off Ver
    2007
    Posts
    12

    Red face Re: combine two sheets into one

    I have requested to add a new column with the employee name to worksheet 1
    now I have the employee name on both worksheets.

    what do I need to do now to get a final sheet with the code# and amount.

    thank for your help!!

  17. #17
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: combine two sheets into one

    Simply use a pivot table

  18. #18
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: combine two sheets into one

    With VLookup and after that a pivot table.

    Make sure if you add the wanted result in your file, that the values are correct; it makes the life of a forummember a lot easier.

  19. #19
    Registered User
    Join Date
    06-15-2015
    Location
    usa
    MS-Off Ver
    2007
    Posts
    12

    Re: combine two sheets into one

    thank you very much all

  20. #20
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: combine two sheets into one

    if the question is solved, will you mark it as solved.

    you can add rep(utationpoints) to the ones who helped you by clicking on the star on the left side.

  21. #21
    Registered User
    Join Date
    06-15-2015
    Location
    usa
    MS-Off Ver
    2007
    Posts
    12

    Smile Merge multiple worksheet in one master Sheet

    Hi,
    I want to merge multiple worksheets (twenty monthly) into a single master worksheet,
    the worksheets tab are called by the date they are sent for approval, i.e. June 15, June 16. and so on.
    Once all the data is in one sheet, I do totals by Employee Name (about 21 employees)

    I've been doing this by using copy and paste command, but this method is time-consuming and easy to duplicate information.
    The goal is to prepare a report showing the total by employee name.
    Note: I am not familiar with VBA

    all worksheets have the same columns in this order:
    vendor Name / Invoice# / Amount / EE Name

    Thank you in advance for your help.
    Attached Files Attached Files

  22. #22
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: combine two sheets into one

    With the macro below.

    See the attached file.

    Please Login or Register  to view this content.

+ 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. How to combine two sheets
    By thisara in forum Excel General
    Replies: 1
    Last Post: 12-05-2014, 01:22 AM
  2. Combine sheets
    By a1b2c3d4e5f6g7 in forum Excel General
    Replies: 7
    Last Post: 10-04-2014, 07:39 AM
  3. [SOLVED] Combine sheets from different sheets into one sheet.
    By visha_1984 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2013, 02:04 AM
  4. Combine two sheets
    By vonborge in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2013, 10:24 PM
  5. [SOLVED] Combine two sheets
    By rose4emi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-20-2012, 02:37 AM

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