All solutions General QuestionsPIVOT TABLE Expert Solution – General Questions on Pivot Table

Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need help to rearrange data in excel. I have 7 columns with days of the week, the 8th column is weekend date. The rows are hours. What I want is a list of dates and the associated hours next to it so I can run a pivot table easily off it
Solved by T. W. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 25/06/2018 - 10:20
Hello
User 25/06/2018 - 10:20
Hello
User 25/06/2018 - 10:20
Can I draft what my problem is?
Excelchat Expert 25/06/2018 - 10:20
Yes please ...
User 25/06/2018 - 10:23
Sorry that should be a date
User 25/06/2018 - 10:24
I only have the week ending date
Excelchat Expert 25/06/2018 - 10:24
Ok
User 25/06/2018 - 10:24
the figures are hours worked across projects
Excelchat Expert 25/06/2018 - 10:24
I see
Excelchat Expert 25/06/2018 - 10:25
So What you need to do ?
User 25/06/2018 - 10:25
I want to run a pivot table to get a daily view
User 25/06/2018 - 10:25
but don't have the dates per day
User 25/06/2018 - 10:25
only the week ending date
User 25/06/2018 - 10:25
So I need to be able to rearrange the data in a list
User 25/06/2018 - 10:26
Project / Employee / Date / Hours
Excelchat Expert 25/06/2018 - 10:26
We can insert the dates at a row above the days
Excelchat Expert 25/06/2018 - 10:28
Or you need to insert a columns beside the hours
User 25/06/2018 - 10:28
Can I get a pivot table off it that way?
User 25/06/2018 - 10:29
I'd want an entity that is called "Date" and then I would have a row of all the dates, and the Project/Employee in the first column
User 25/06/2018 - 10:32
Can I show you what I'd ideally like the data to look like?
Excelchat Expert 25/06/2018 - 10:32
Yes please
User 25/06/2018 - 10:36
I've just put in the working days but it can include saturday and sunday
User 25/06/2018 - 10:36
So if the data was in this format, I could run a simple pivot off it
Excelchat Expert 25/06/2018 - 10:37
I can make your data like this if you want
User 25/06/2018 - 10:37
Is there a formula to rearrange it?
User 25/06/2018 - 10:37
without too much manual manipulation?
Excelchat Expert 25/06/2018 - 10:38
I can make a formula
User 25/06/2018 - 10:38
how would the formula work?
User 25/06/2018 - 10:38
would it calculate back from the week ending date?
Excelchat Expert 25/06/2018 - 10:38
Let me make it first
User 25/06/2018 - 10:39
oky
User 25/06/2018 - 10:50
I've created the pivot table if that helps?
Excelchat Expert 25/06/2018 - 10:50
Ok
User 25/06/2018 - 10:51
:)
Excelchat Expert 25/06/2018 - 10:51
I'll attach the file for you with the formulas that convert your table to the table needed
Excelchat Expert 25/06/2018 - 10:52
[Uploaded an Excel file]
Excelchat Expert 25/06/2018 - 10:52
At the sheet2 there is the formulas
User 25/06/2018 - 10:53
is it able to pick up the hours?
Excelchat Expert 25/06/2018 - 10:53
Yes i'll do
Excelchat Expert 25/06/2018 - 10:53
I just forget this
Excelchat Expert 25/06/2018 - 10:54
I am working on it
Excelchat Expert 25/06/2018 - 11:05
[Uploaded an Excel file]
User 25/06/2018 - 11:06
wow
User 25/06/2018 - 11:07
you did it!
User 25/06/2018 - 11:07
how did you do that?
User 25/06/2018 - 11:07
I work as a project management officer in Operations
User 25/06/2018 - 11:07
I use excel quite a lot
User 25/06/2018 - 11:07
But this was quite complex for me
Excelchat Expert 25/06/2018 - 11:08
It depend on the data structure
User 25/06/2018 - 11:09
I just couldn't figure this out
User 25/06/2018 - 11:09
I knew it could be done but I just don't have the experience of using anything more complex than sumifs
Excelchat Expert 25/06/2018 - 11:10
This done using the index, if and sum formulas
User 25/06/2018 - 11:11
I think I better learn IF and INDEX
Excelchat Expert 25/06/2018 - 11:11
Because you have 7 columns So I make the helper column at A
Excelchat Expert 25/06/2018 - 11:12
And according this column I just use the index formula
User 25/06/2018 - 11:12
I will study the Inex formula
User 25/06/2018 - 11:12
You have been a great help
User 25/06/2018 - 11:12
How did you get so good with Excel?
Excelchat Expert 25/06/2018 - 11:12
s
User 25/06/2018 - 11:12
what do you do?
Excelchat Expert 25/06/2018 - 11:13
I work a lot on excel
Excelchat Expert 25/06/2018 - 11:13
and form the internet
Excelchat Expert 25/06/2018 - 11:14
There are alot of materials on the net and youtube that learn excel
User 25/06/2018 - 11:14
I have been searching hw to do it but I didn't know what formula to use :(
Excelchat Expert 25/06/2018 - 11:14
But you need to practice more and more
User 25/06/2018 - 11:14
yes you are right
Excelchat Expert 25/06/2018 - 11:15
You need to know every thing about each formula so you can combine this formulas to generate new formulas
User 25/06/2018 - 11:15
Can I be in touch with you again/
User 25/06/2018 - 11:15
?
Excelchat Expert 25/06/2018 - 11:16
You can post your problems here but may be some one else contact with you
User 25/06/2018 - 11:16
I hope I get you
User 25/06/2018 - 11:16
:)
Excelchat Expert 25/06/2018 - 11:16
me too
User 25/06/2018 - 11:16
thank you so much
User 25/06/2018 - 11:16
you have been amazing
User 25/06/2018 - 11:17
I love excel, i wish to learn more
Excelchat Expert 25/06/2018 - 11:17
Thank you
User 25/06/2018 - 11:17
I will study your formula
User 25/06/2018 - 11:17
to learn index and if formula
User 25/06/2018 - 11:17
i will see on youtube how they function
Excelchat Expert 25/06/2018 - 11:18
usually index is used with match formula so study the two.
User 25/06/2018 - 11:18
excellent i will
User 25/06/2018 - 11:18
thank you so mmuch
User 25/06/2018 - 11:18
from London :)
Excelchat Expert 25/06/2018 - 11:19
You're welcome
Excelchat Expert 25/06/2018 - 11:19
Have a nice day..
User 25/06/2018 - 11:19
you too!
User 25/06/2018 - 11:19
so lovely to meet you
Excelchat Expert 25/06/2018 - 11:19
Me too.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc