Need help modifying a table
- RSS-Feed abonnieren
- Thema als neu kennzeichnen
- Thema als gelesen kennzeichnen
- Diesen Thema für aktuellen Benutzer floaten
- Lesezeichen
- Abonnieren
- Stummschalten
- Drucker-Anzeigeseite
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
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!
- Beschriftungen:
- Best Practices
- Date Time
- Join
- Tips and Tricks
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
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 😀👍
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
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
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
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
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
@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!
- Als neu kennzeichnen
- Lesezeichen
- Abonnieren
- Stummschalten
- RSS-Feed abonnieren
- Kennzeichnen
- Moderator informieren
Thank you so much for this suggestion, it works really well with my workflow.