Forum Discussion
Is this possible with Pivot Tables, or...
I’ve a time sheet of hours worked by volunteers that I’d like to analyze.
Current:
Name | 3/1/21 | 3/2/21 |
Bob | 3 | 2.5 |
Sally | 1 | 5 |
Desired:
Name | Date | Hours |
Bob | 3/1/21 | 3 |
Sally | 3/1/21 | 1 |
Bob | 3/2/21 | 2.5 |
Sally | 3/2/21 | 5 |
How do I do this efficiently?
Is this a Pivot Table thing? I'm a novice there but could also do an Excel add-in using JavaScript, but that seems like overkill.
JCornelison Easiest done with Power Query (Windows only!)
Query the table (as in Current:)
Select the Name column
Right-click and select "Unpivot other columns"
Close and load back to Excel.
More details in the attached link.
6 Replies
As variant
with
=LET(dates, $C$2:$E$2, names, $B$3:$B$4, data, $C$3:$E$4, nDates, COLUMNS(dates), nNames, ROWS(names), k, SEQUENCE(nDates*nNames), CHOOSE({1,2,3}, INDEX(names,INT((k-1)/nDates)+1), INDEX(dates,,MOD(k-1,nDates)+1), INDEX(data,INT((k-1)/nDates)+1,MOD(k-1,nDates)+1)) )- nice one
- u need power query for that, then u will unpivot date & hrs
JCornelison Easiest done with Power Query (Windows only!)
Query the table (as in Current:)
Select the Name column
Right-click and select "Unpivot other columns"
Close and load back to Excel.
More details in the attached link.
Many roads lead to Rome.
This is also the case with Excel, there are many ways to find a solution ... even tailor made.
In the inserted file you can see it with a very simple pivot as an example.
I also wish you a lot of fun with Excel.
Nikolino
I know that I don't know (Socrates)
NikolinoDE Thanks, but what I'm seeing from your pivot table doesn't match my "Desired" table. I want each column of hours to result in new rows, basically going from a wide/short table to a tall/narrow table.
I can sort of do this by putting all the columns (name + date 1 + date 2) into the Pivot Table's Rows box, and then adjusting the Field Settings to "No Subtotals" and "Show Item Labels in Tabular Form", and then copy & pasting values only into a new sheet. But the columns arent properly labelled, and I need to automate this as I've 7 days per sheet and and 52 Sheets (1 per week). (Yes, time for volunteers working the COVID crisis, currently over 25K hours total!)
