Hello! I have this data below and I want to edit the layout. Can I use transpose to do this?
Input
Hours | Maker / Checker | Process | 2/1/2021 | 2/2/2021 | 2/3/2021 | 2/4/2021 |
19 | M | Sign | 16 | 21 | 24 | 13 |
3 | M | Template | 61 | 1 | 5 | 3 |
3 | M | Inquiries | 8 | 20 | 6 | 9 |
10 | M | 0 | 0 | 0 | 0 | |
Hours | Maker / Checker | Process | 1/4/2021 | 1/5/2021 | 1/6/2021 | 1/7/2021 |
19 | M | Sign | 4 | 13 | 16 | 18 |
3 | M | Template | 123 | 41 | 20 | 0 |
3 | M | Inquiries | 4 | 8 | 0 | 3 |
10 | M | 0 | 0 | 0 | 0 |
Output - something like this
Hours | Maker / Checker | Process | Name | Value |
10 | M | 2/1/2021 | 0 | |
10 | M | 2/2/2021 | 0 | |
10 | M | 2/3/2021 | 0 | |
10 | M | 2/4/2021 | 0 | |
10 | M | 1/4/2021 | 0 | |
10 | M | 1/5/2021 | 0 | |
10 | M | 1/6/2021 | 0 | |
10 | M | 1/7/2021 | 0 | |
3 | M | Inquiries | 2/1/2021 | 8 |
3 | M | Inquiries | 2/2/2021 | 20 |
3 | M | Inquiries | 2/3/2021 | 6 |
3 | M | Inquiries | 2/4/2021 | 9 |
3 | M | Inquiries | 1/4/2021 | 4 |
3 | M | Inquiries | 1/5/2021 | 8 |
3 | M | Inquiries | 1/6/2021 | 0 |
3 | M | Inquiries | 1/7/2021 | 3 |
19 | M | Sign | 2/1/2021 | 16 |
19 | M | Sign | 2/2/2021 | 21 |
19 | M | Sign | 2/3/2021 | 24 |
19 | M | Sign | 2/4/2021 | 13 |
19 | M | Sign | 1/4/2021 | 4 |
19 | M | Sign | 1/5/2021 | 13 |
19 | M | Sign | 1/6/2021 | 16 |
19 | M | Sign | 1/7/2021 | 18 |
3 | M | Template | 2/1/2021 | 61 |
3 | M | Template | 2/2/2021 | 1 |
3 | M | Template | 2/3/2021 | 5 |
3 | M | Template | 2/4/2021 | 3 |
3 | M | Template | 1/4/2021 | 123 |
3 | M | Template | 1/5/2021 | 41 |
3 | M | Template | 1/6/2021 | 20 |
3 | M | Template | 1/7/2021 | 0 |
Thank you 🙂
I think the problem is that you have two sets of dates... Ideally you'd have one set of dates across the top and empty fields where no information exists - then a simple transpose with key columns of Hours, Maker/Checker, and Process would give you what you want (and you can filter out the blank entries.).
With data like this - what I'd do is still transpose with those three key columns then transfer over to the name column where the values are the new dates. Then use a multi-row to change out the old dates and finally a filter to take out the row with dates in the value fields...
I've attached a workflow that shows how to do that.
User | Count |
---|---|
17 | |
15 | |
15 | |
8 | |
6 |