Need help modifying a table

[aidanf]

Hi,

I'm working with an excel file that contains each employee's hours, as well as the associated work code for those hours. 

The table looks a bit like this... 

Name	Work Code	Week 1 Hours	Week 2 Hours	Week 3 Hours	Week 4 Hours
Amy	1	2	3	2	3
Bob	1	3	2	4	5
Bob	3	4	5	4	3
Claire	2	0	1	2	1
Claire	3	2	1	1	2
Claire	4	3	3	3	3

I want to modify this table so that each employee has a unique row for each of the 4 work codes, even if they work 0 hours on that code.

It should something like this:

Name	Work Code	Week 1 Hours	Week 2 Hours	Week 3 Hours	Week 4 Hours
Amy	1	2	3	2	3
Amy	2	0	0	0	0
Amy	3	0	0	0	0
Amy	4	0	0	0	0
Bob	1	3	2	4	5
Bob	2	0	0	0	0
Bob	3	4	5	4	3
Bob	4	0	0	0	0
Claire	1	0	0	0	0
Claire	2	0	1	2	1
Claire	3	2	1	1	2
Claire	4	3	3	3	3

Any tips on how I can get to this?

Thanks!

[atcodedog05]

Hi @aidanf ,

Here is a workflow for the task

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[jdunkerley79]

I'd suggest:

First, using summarise tools to make the complete list of Names and Work Codes

Next, using append fields to create a full set

A join multiple tool allows for creating an outer join to make the full table

Finally, a multi-field formula allows filling in the NULLs

Have attached a sample

[AngelosPachis]

Hey @aidanf 

I would suggest a more dynamic approach that would pick up any work code, even if that is greater than that you used in your example

Let me know if that works for you or if anything is unclear.

Cheers,

Angelos

[AngelosPachis]

@jdunkerley79 That's a great approach, couldn't get the Join Multiple tool to work for my workflow, but now I know why. Thanks for that, great solution!

[aidanf]

Thank you so much for this suggestion, it works really well with my workflow.