SOLVED

Is this possible with Pivot Tables, or...

New Contributor

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.

6 Replies

@JCornelison 

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!)

best response confirmed by JCornelison (New Contributor)
Solution

@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.

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882... 

u need power query for that, then u will unpivot date & hrs

@JCornelison 

As variant

image.png

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