Pivot Tables

[AlanBeech]

Hi All

Looking for help on a big problem I have.

I have data for Depot departure times and store arrival times for each day in the week. In total there are 1892 deliveries for 304 stores accross the 7 days. I have tried to use a pivot table to take the data from a 1 column going down the sheet top ut all the data accross the spreadsheet showing the times for each day. The main problem is the data starts in the wrong day in the pivot table and because there may be 2 deliveries in the same day but at different ends of the day, it misses the 2nd delivery day out. Hope this makes sence and below is an example.

E.G

Saturday
Store: Depot Depart: Store Arrival:
ABC1 22:50 00:16
Sunday
ABC1 21:54 23:25

Because there are 2 deliveries in the Sunday it misses out the second depart/delivery data. The pivot table also puts some of the data in the wrong day when there are deliveries as above.

When delivery times are in each day, there is no problem.

Hope someone can help.

 

[JHB]

Do you have a database with sample data to post, (zip it because you are under the limit of 10 post).

How do you want it grouped - on store or ??

 

[AlanBeech]

Many thanks for your time and help. Atatched data. Needs to be in day order accross the table.

 

[JHB]

... Needs to be in day order accross the table.

Do you mean so, (else show how)?

 

[AlanBeech]

Hi, thanks for your time. ooks like the week would be top to bottom. I need the data to go accross the shhet left to right. E.G. Starting showing the delivery days and time on a Sunday through to Saturday.

The headings are correct, but as an example?

Long Eaton Monday 06:54, Tuesday 06:54 etc so the table shows all the store delivery times accross in days rather than going down.

Hope I have made myself understood.

Kind regards

Alan

 

[JHB]

Hope I have made myself understood.

Sorry, no not for me, could you set an example up in Excel, (with the data show below), how you want it.

Long Eaton Monday 01:54, Monday 06:54
Long Eaton Monday 06:00, Tuesday 06:54
Merry Hill Saturday 02:00 Monday 08:00

 

[AlanBeech]

Hi

Hope this is ok for you.

Any idea why all of a sudden all the data is showing 00:00. I have formatted the data as hh:mm

Thanks again

 

[JHB]

Yes I see, but I think you will never get this with a crosstab.
And I doubt you will be able to keep different hours out of each other in the manner as shown in your spreadsheet.
I have made an example of the above statement, which I attach.

Say departure on Sundays (01:10) and arrival on Monday (18:00), while there is another departure on Mondays, when you look at the table it seems that it arrives on Monday, but it's actually the one who departed on Sunday. But of course it's you who decide what you want.
In the example are the different colors together.