How to get data a proper data table from software generate report

Question

Hi,

I've attached an excel spreadsheet that has 2 tabs. 1 with some dummy information in the format it gets outputted and the other with how we need to be able to see the data.

I've tried using a record id for the information in each line and then doing a multi join on the id but because some of the sections have different numbers of rows under description they end up in the wrong record id for the employee.

How could i do this another way that will link the information to the name and pay period so that regardless of how many items there are under description it will still pull the correct information for the columns needed in the other format?

Thanks a lot

Dummy Info.xlsx

DavidP · Accepted Answer

My approach below is a pretty manual one - it uses filter tools to only pick the ones you want from the description section. Initially, I made the assumption that 2 empty lines is the separator between 2 record sets.

I haven't mapped all the fields, but you get the idea.