+ Reply to Thread
Results 1 to 5 of 5

Combining data from multiple sheets

  1. #1
    gmayeaux@lctcs.net
    Guest

    Combining data from multiple sheets

    I have three seperate spreadsheets with some common data on them. Each
    spreadsheet contains an employee's name, check number and check date.
    Sheet 1 has one row of data for each unique employee name/check
    date/check number combination but the other two sheets have multiple
    rows of data for each unique employee name/check date/check number
    combination.

    Sample Data
    Sheet 1:
    Name Check # Check Date Gross Pay Total Ded Total Tax Net
    Pay
    John Doe 210356 2005-10-07 20.00 7.00 3.00
    10.00

    Sheet 2: (First three columns are the same as Sheet 1)
    Name Check # Check Date Ded Type Ded Code Ded Class
    Current Ded
    John Doe 210356 2005-10-07 Life LFE01 EE Portion
    1.00
    John Doe 210356 2005-10-07 Health HLTH4 EE Portion
    1.00
    John Doe 210356 2005-10-07 Health HLTH4 ER Portion
    1.00
    John Doe 210356 2005-10-07 Retirement RET03 EE Portion
    1.50
    John Doe 210356 2005-10-07 Retirement RET03 ER Portion
    2.50

    Sheet 3: (First three columns are the same as Sheet 1)
    Name Check # Check Date Tax Class State Current Tax
    John Doe 210356 2005-10-07 FICA/EE US 0.50
    John Doe 210356 2005-10-07 FICA/ER US 0.50
    John Doe 210356 2005-10-07 Withholding US 1.00
    John Doe 210356 2005-10-07 Withholding LA 1.00


    I need to combine the three spreadsheets into one as follows...

    Name Check# Check Date Gross Pay Total Ded. Total Tax Net Pay
    Life Health-EE Health-ER Retirement-EE Retirement-ER FICA-EE
    FICA-ER Withholding-US Withholding-LA

    John Doe 210356 2005-10-07 20.00 7.00 3.00 10.00
    1.00 1.00 1.00 1.50 2.50 0.50
    0.50 1.00 1.00


    Is this even possible, if so can someone help me out with this because
    I am new to macros and I have no clue where to begin.

    Thanks in advance!
    Gerald


  2. #2
    John
    Guest

    Re: Combining data from multiple sheets

    Hi Gerald,

    I looked at this last night and didn't reply imediately with a question just
    in case anyone else had a straight answer for you. However, since no
    has........

    Can you explain how you get to the final values from (what I assume is)
    sheet 2?

    ie How does Ded Code and Ded Class relate to: "1.00 1.00 1.00 1.50 2.50"

    It feels like we've only got part of the puzzle here........any more clues?

    Best regards

    John

    <gmayeaux@lctcs.net> wrote in message
    news:1129827877.796968.147800@g47g2000cwa.googlegroups.com...
    >I have three seperate spreadsheets with some common data on them. Each
    > spreadsheet contains an employee's name, check number and check date.
    > Sheet 1 has one row of data for each unique employee name/check
    > date/check number combination but the other two sheets have multiple
    > rows of data for each unique employee name/check date/check number
    > combination.
    >
    > Sample Data
    > Sheet 1:
    > Name Check # Check Date Gross Pay Total Ded Total Tax Net
    > Pay
    > John Doe 210356 2005-10-07 20.00 7.00 3.00
    > 10.00
    >
    > Sheet 2: (First three columns are the same as Sheet 1)
    > Name Check # Check Date Ded Type Ded Code Ded Class
    > Current Ded
    > John Doe 210356 2005-10-07 Life LFE01 EE Portion
    > 1.00
    > John Doe 210356 2005-10-07 Health HLTH4 EE Portion
    > 1.00
    > John Doe 210356 2005-10-07 Health HLTH4 ER Portion
    > 1.00
    > John Doe 210356 2005-10-07 Retirement RET03 EE Portion
    > 1.50
    > John Doe 210356 2005-10-07 Retirement RET03 ER Portion
    > 2.50
    >
    > Sheet 3: (First three columns are the same as Sheet 1)
    > Name Check # Check Date Tax Class State Current Tax
    > John Doe 210356 2005-10-07 FICA/EE US 0.50
    > John Doe 210356 2005-10-07 FICA/ER US 0.50
    > John Doe 210356 2005-10-07 Withholding US 1.00
    > John Doe 210356 2005-10-07 Withholding LA 1.00
    >
    >
    > I need to combine the three spreadsheets into one as follows...
    >
    > Name Check# Check Date Gross Pay Total Ded. Total Tax Net Pay
    > Life Health-EE Health-ER Retirement-EE Retirement-ER FICA-EE
    > FICA-ER Withholding-US Withholding-LA
    >
    > John Doe 210356 2005-10-07 20.00 7.00 3.00 10.00
    > 1.00 1.00 1.00 1.50 2.50 0.50
    > 0.50 1.00 1.00
    >
    >
    > Is this even possible, if so can someone help me out with this because
    > I am new to macros and I have no clue where to begin.
    >
    > Thanks in advance!
    > Gerald
    >




  3. #3
    gmayeaux@lctcs.net
    Guest

    Re: Combining data from multiple sheets

    The data from the three sheets comes from three seperate queries in our
    payroll system. If you were to look at Sheet1 for the above referenced
    employee you find that the Total Ded. amount for that employee ties to
    the sum of the employees' deductions on Sheet2 and the sum of the
    employees' taxes on Sheet3 ties to the Total Tax on Sheet 1 for that
    employee. So in esence, Sheet one is a summary and Sheet2 is the
    detail of what makes up the Total Ded and Sheet3 is the detail of what
    makes up the Total Tax.

    To further complicate the matter, not everyone has the same deductions
    (or the same # of deductions) and in some cases not the same for taxes.
    For example, Employee A may have one retirement plan while employee B
    has another and I am "trying" to have each different deduction (unique
    combination of deduction code & deduction type) in a seperate column so
    that Employee A would have a number in the column relating to the
    retirement plan he belongs to while employee B would have nothing in
    that column but their retirment would be placed in the appropriate
    column.

    Please let me know if you need more info, or have any questions.

    Thanks!
    Gerald


  4. #4
    John
    Guest

    Re: Combining data from multiple sheets

    Hi Gerald,

    OK, I see. One last question then is, in your example, is this the maximum
    number of columns (in the fourth sheet)?

    Let me know and I'll take a look over the weekend.

    Best regards

    John

    <gmayeaux@lctcs.net> wrote in message
    news:1129905237.187933.93480@g14g2000cwa.googlegroups.com...
    > The data from the three sheets comes from three seperate queries in our
    > payroll system. If you were to look at Sheet1 for the above referenced
    > employee you find that the Total Ded. amount for that employee ties to
    > the sum of the employees' deductions on Sheet2 and the sum of the
    > employees' taxes on Sheet3 ties to the Total Tax on Sheet 1 for that
    > employee. So in esence, Sheet one is a summary and Sheet2 is the
    > detail of what makes up the Total Ded and Sheet3 is the detail of what
    > makes up the Total Tax.
    >
    > To further complicate the matter, not everyone has the same deductions
    > (or the same # of deductions) and in some cases not the same for taxes.
    > For example, Employee A may have one retirement plan while employee B
    > has another and I am "trying" to have each different deduction (unique
    > combination of deduction code & deduction type) in a seperate column so
    > that Employee A would have a number in the column relating to the
    > retirement plan he belongs to while employee B would have nothing in
    > that column but their retirment would be placed in the appropriate
    > column.
    >
    > Please let me know if you need more info, or have any questions.
    >
    > Thanks!
    > Gerald
    >




  5. #5
    gmayeaux@lctcs.net
    Guest

    Re: Combining data from multiple sheets

    Yes the fourth sheet would conatain many columns one for each unique
    (Ded. Code/ Ded. Type combination) and one for each unique (Tax
    Class/State combination) but each check number would be only one row of
    data with many columns. If you'd like I can send a sample workbook but
    I'm not sure how to post it here.

    Thanks again!
    Gerald


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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