|
Wondering if this is possible with a Macro or if another solution is better...
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 |
Wondering if this is possible with a Macro or if another solution
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. -- Gary''s Student " wrote: 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 |
Wondering if this is possible with a Macro or if another solut
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 "Gary''s Student" wrote: 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. -- Gary''s Student " wrote: 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 |
| All times are GMT +1. The time now is 12:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com