Wondering if this is possible with a Macro or if another solution is better...

[gmayeaux]

I have tried poting this question in the excel.misc group but I have
had no advice on how to do this. My first thought was to use a VLookup
and HLookup but I am unsure of how to go about doing this and if this
is even the best way. So now I am posing the question as to whether or
not this can be done with a Macro.

I have three seperate spreadsheets. All three spreadsheets have
Employee Name, Check # and Check Date in common. I need to combine the
three spreadsheets by check # all on one line for each check number.
For instance, the final spreadsheet would have Employee Name, Check #,
Check Date, Gross Pay, Total Deductions, Total Withholding, Net Pay,
Deduction A, Deduction B,...Witholding A, and so on. Below is a sample
of the data.


Spreadsheet1:
Check # Check Dt Name Tot Gross Total Ded. Total Taxes Net Pay End Date
123456 2005-09-16 Doe, John 1,794.62 196.40 206.38 1,391.84
2005-09-25

Spreadsheet2:
Check # Check Dt Name Plan Typ Deductn Cd Ded Class Curr Dedn End Date
123456 2005-09-16 Doe, John Life BASLIF Before-Tax 1.15 2005-09-25
123456 2005-09-16 Doe, John Medical GRBOSN Before-Tax 51.68 2005-09-25
123456 2005-09-16 Doe, John Retirement
Plans ORPAET Before-Tax 143.57 2005-09-25
123456 2005-09-16 Doe, John Retirement
Plans TRSL01 Before-Tax 0.00 2005-09-25
123456 2005-09-16 Doe, John Life BASLIF Nontaxable
Benefit 1.15 2005-09-25
123456 2005-09-16 Doe, John Medical GRBOSN Nontaxable
Benefit 155.04 2005-09-25
123456 2005-09-16 Doe, John Retirement Plans ORPAET Nontaxable
Benefit 285.34 2005-09-25
123456 2005-09-16 Doe, John Retirement Plans TRSL01 Nontaxable
Benefit 0.00 2005-09-25

Spreadsheet3:
Check # Check Dt Name Tax Class St Curr Tax End Date
123456 2005-09-16 Doe, John FICA Med Hospital Ins /
EE $U 25.26 2005-09-25
123456 2005-09-16 Doe, John Withholding $U 147.04 2005-09-25
123456 2005-09-16 Doe, John FICA Med Hospital Ins /
ER $U 25.26 2005-09-25
123456 2005-09-16 Doe, John Withholding LA 34.08 2005-09-25


Any help would be GREATLY appreciated!

Gerald

 

[Gary''s Student]

If your check numbers are unique, then there is a simple solution. Start
with any of your three sheets. Use VLOOKUP() based upon check number on the
other two sheets to capture information associated with the same check.

 

[Anton]

And even if the check numbers are not unique it is possible, although
somewhat more complicated:
- find a matching check number
- check if the employee name matches - if not, continue searching for the
check number
- check if the date matches - if not, continue searching for the check number
- only if all three match combine the data from the other columns

Anton