Translating Payroll Export to a more organized Sheet

[tacoFredtaco]

Good evening,

I have a system that I can export my staff's payroll information into a .csv format and paste to an excel spreadsheet. I have been trying (unsuccessfully) to write up formulas that can translate that export into the format that our payroll department uses, so that we can eliminate the painstaking task of manually entering hundreds of employee's information.

At first I tried utilizing Vlookup, but as the export only lists the name for the most recent date the employee worked and then leaves blank spaces on prior dates I couldn't do it.

The system also does not leave a line for each date, so some employees information is 1 line long while others could have 10 lines.

I'm feeling very defeated...any suggestions would help. I have attached an example workbook with both the format that our system exports the csv and the sheet that our payroll department needs the information to be in.

[kvsrinivasamurthy]

Helper column H is used in Pay Export sheet.
In H4 then copy down

Please Login or Register  to view this content.

In Pay Roll sheet
In D2

Please Login or Register  to view this content.

IN E2

Please Login or Register  to view this content.

In F2

Please Login or Register  to view this content.