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.
