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