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

  • Thread starter Thread starter gmayeaux
  • Start date Start date
G

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
 
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.
 
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
 
Back
Top