We have raw data with employee hours and dates that we need to convert to a specific format for billing. When we do it the old-fashioned way, it is a lot of copy/paste and then double verification that we copied and pasted correctly. One project alone takes at least 8 hours to get organized properly.
I have an example raw data file and an example finished file. I know that some look-up formulas with pre-named tabs might be one solution, but looking for maybe a VBA script that will do this automatically.
The raw data looks like this:
And the finished product looks like this:
I have an example raw data file and an example finished file. I know that some look-up formulas with pre-named tabs might be one solution, but looking for maybe a VBA script that will do this automatically.
The raw data looks like this:
| Trans Date | Employee Name | Reg Time | Ovt Time |
5/1/2023 | Lucero,Adam B | 8 | 0 |
5/1/2023 | Leiding,Monte J | 4 | 0 |
5/1/2023 | Banks,TaCanunpa W | 8 | 0 |
5/1/2023 | Jaramillo,Zackary H | 7 | 0 |
5/1/2023 | Guzman,Steven M | 8 | 0 |
5/1/2023 | Rodriguez,Jose R | 4 | 0 |
5/1/2023 | Serna,Ryan S | 8 | 0 |
5/1/2023 | Quintana,Anthony Ray | 7.5 | 0 |
5/1/2023 | Sisneros,Joshua R | 7.5 | 0 |
5/1/2023 | Sisneros,William R | 8 | 0 |
5/1/2023 | Mancha,Martin M | 5.5 | 0 |
5/1/2023 | Chavez,Fernando | 7.5 | 0 |
5/1/2023 | Calderon,Luis C | 7.5 | 0 |
5/1/2023 | Montez,Omar I | 3 | 0 |
5/1/2023 | Dennison,Joshua A | 4 | 0 |
5/1/2023 | Grajeda Herrera,Pedro A | 4 | 0 |
5/1/2023 | Trotter,John W | 8 | 0 |
5/1/2023 | Teupell,Ralph | 6.5 | 0 |
5/1/2023 | Salazar,Phillip W | 2.75 | 0 |
5/1/2023 | Davis,Harry J | 7.5 | 0 |
5/1/2023 | Favela-Legarda,Alvaro | 3 | 0 |
5/1/2023 | Perez,Eric J | 3 | 0 |
5/1/2023 | Jim,Myron B | 7.5 | 0 |
5/1/2023 | Gurule,Adrian J | 7.5 | 0 |
5/2/2023 | Lucero,Adam B | 8 | 0 |
5/2/2023 | Leiding,Monte J | 3 | 0 |
5/2/2023 | Banks,TaCanunpa W | 6.5 | 0 |
5/2/2023 | Jaramillo,Zackary H | 6 | 0 |
5/2/2023 | Guzman,Steven M | 8 | 0 |
5/2/2023 | Rodriguez,Jose R | 7 | 0 |
5/2/2023 | Serna,Ryan S | 7 | 0 |
5/2/2023 | Quintana,Anthony Ray | 6.5 | 0 |
5/2/2023 | Sisneros,Joshua R | 6.5 | 0 |
5/2/2023 | Sisneros,William R | 8 | 0 |
And the finished product looks like this:
| ROUSH | |||||||
| PROJECT TITLE | TORC Robotics, Inc. | INVOICE PERIOD | |||||
| DTNA REF# | START | END | |||||
| PURCHASE ORDER# | 5/1/2023 | 5/31/2023 | |||||
| ROUSH REF# | 130437 | ||||||
| EXPENSE DESCRIPTION | RATE | QTY | TOTAL | ||||
| Vehicle | |||||||
| NO ID | TYPE | DATE | |||||
| REG Hours | 5/1/2023 | Banks | $48.60 | 8.00 | $388.80 | ||
| REG Hours | 5/2/2023 | Banks | $48.60 | 6.50 | $315.90 | ||
| REG Hours | 5/3/2023 | Banks | $48.60 | 7.50 | $364.50 | ||
| REG Hours | 5/4/2023 | Banks | $48.60 | 7.50 | $364.50 | ||
| REG Hours | 5/5/2023 | Banks | $48.60 | 3.50 | $170.10 | ||
| REG Hours | 5/6/2023 | Banks | $48.60 | 7.00 | $340.20 | ||
| REG Hours | 5/8/2023 | Banks | $48.60 | 6.50 | $315.90 | ||
| REG Hours | 5/8/2023 | Banks | $48.60 | 3.50 | $170.10 | ||
| REG Hours | 5/9/2023 | Banks | $48.60 | 7.50 | $364.50 | ||
| REG Hours | 5/10/2023 | Banks | $48.60 | 7.50 | $364.50 | ||
| REG Hours | 5/11/2023 | Banks | $48.60 | 7.50 | $364.50 | ||
| REG Hours | 5/12/2023 | Banks | $48.60 | 7.50 | $364.50 | ||
| REG Hours | 5/15/2023 | Banks | $48.60 | 3.50 | $170.10 | ||
| REG Hours | 5/15/2023 | Banks | $48.60 | 2.00 | $97.20 | ||
| REG Hours | 5/16/2023 | Banks | $48.60 | 7.50 | $364.50 | ||
| REG Hours | 5/17/2023 | Banks | $48.60 | 7.00 | $340.20 | ||
| REG Hours | 5/18/2023 | Banks | $48.60 | 3.00 | $145.80 | ||
| REG Hours | 5/18/2023 | Banks | $48.60 | 7.00 | $340.20 | ||
| REG Hours | 5/19/2023 | Banks | $48.60 | 2.00 | $97.20 | ||
| REG Hours | 5/19/2023 | Banks | $48.60 | 8.00 | $388.80 | ||
| REG Hours | 5/22/2023 | Banks | $48.60 | 4.00 | $194.40 | ||
| REG Hours | 5/22/2023 | Banks | $48.60 | 3.00 | $145.80 | ||
| REG Hours | 5/23/2023 | Banks | $48.60 | 7.00 | $340.20 | ||
| REG Hours | 5/24/2023 | Banks | $48.60 | 7.50 | $364.50 | ||
| REG Hours | 5/26/2023 | Banks | $48.60 | 10.50 | $510.30 | ||
| REG Hours | 5/31/2023 | Banks | $48.60 | 3.00 | $145.80 | ||
| TOTAL | $7,533.00 | ||||||
| EXPENSE DESCRIPTION | RATE | QTY | TOTAL | ||||
| Vehicle | |||||||
| NO ID | TYPE | DATE | |||||
| OT Hours | 5/7/2023 | Banks | $72.90 | 10.00 | $729.00 | ||
| OT Hours | 5/13/2023 | Banks | $72.90 | 10.00 | $729.00 | ||
| OT Hours | 5/15/2023 | Banks | $72.90 | 10.00 | $729.00 | ||
| TOTAL | $2,187.00 | ||||||
| MONTH TOTAL | $9,720.00 | ||||||
