Combining data from multiple sheets

[gmayeaux]

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

 

[John]

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]

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

 

[John]

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]

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