How to extract data from messy table format?

[Inactive User]

Hi all,

I've got an output from some payroll software that's got nulls in all the wrong places. I've attached a workflow which contains a mock-up of my data structure and what I want the output to look like (also see screenshots below). I just want to summarise the data like the output (see screenshots below).

Please help, I've pretty spent all day on this and it's driving me nuts.

I want to take this messy table style input:

 

And turn it into the following useable output:

My initial thoughts were that I'd just need to transpose the data, keeping the "Employee ID" and "Name" as key columns and take it from there, but it's immediately created as new problem (see below) because of the structure of that data, where the rows with values don't have an employee ID or a name.

I'm not even sure if this is the right avenue to be exploring, but I've tried to solve this problem by populating the nulls in the employee ID and name columns with the ID and name. I've tried generating rows, multi-row formulas, appending fields to no avail - I'm well and truly stumped with this one.

I'm not even sure if this is the right way to be approaching or thinking about the problem.

 

Any help would be very appreciated!

James

[atcodedog05 · Accepted solution]

Hi @Inactive User 

Here is how you can do it.

Edit: updated workflow with order.

Workflow:

Hope this helps : )

[gabrielvilella · Accepted solution]

Hi, you need first to fill out those nulls for the ID and Name columns, then you can use the transpose. See if my solution is what you are looking for.

[Inactive User]

That's amazing, two neat solutions - thank you both so much!

It was the expression in the multi-row formula that was tripping me up, I need to get some more practice with those.

Thanks again.

[atcodedog05]

Happy to help : ) @Inactive User 

Cheers and have a nice day!