Pivot Table Problem

[Mark McDonough]

Going down the worksheet there could be 50 instances of Hong Kong, 50
instances of Jakarta etc in col B. For each port there is weekly data of
volume transported and the volumes is what makes up the data by port by
mth/yr by supplier. A brief example of the spreadsheet is shown.

I have the following data in a worksheet and I would like to create a pivot
table so that I can select a port and see the total volume for each month by
supplier. I need to be able to select a supplier and obtain all the weekly
volume for that supplier and also be able to see the total volume for all
the months.


ORIGN PORT DATE SUPPLIER 1, SUPPLIER 2, SUPPLIER 3, SUPPLIER 4,
SUPPLIER 5 etc
Hong Kong Mar2006 10000 15000 20000 12000 9000
Hong Kong Apr2006 9000 2000 18000 6000 7000
Hong Kong May2006 11000 9000 14000 8000 6000
Hong Kong Jun2006 and so on............
Hong Kong Jul2006
Hong Kong Aug2006
Singapore Mar2006
Singapore Apr2006
Singapore May2006
Singapore Jun2006
Singapore Jul2006
Singapore Aug2006
Jakarta Mar2006
Jakarta Apr2006
Jakarta May2006
Jakarta Jun2006
Jakarta Jul2006
Jakarta Aug2006


The outcome should be able to do the following:

Select a port and see the volume by supplier by port or
Select a supplier and see the volume by month by port - Greatest priority
Select a month and see the volume by each supplier.

I'm not sure I've made this very clear. I keep on getting meaningless
information when I do this and a very messy looking pivot table.

Is someone able to help me?

 

[Roger Govier]

Hi Mark

You need to get your data into a normal format for use with PT's which
would be to have a series of rows with

Port Month Customer Value
HK Mar06 Cust1 1000
HK Mar06 Cust2 1500
etc.

John Walkenbach has some help on how to turn your data format to the
required layout at
http://j-walk.com/ss/excel/usertips/tip068.htm

There is also a downloadable macro to carry out the task

Once you have your data in the format described above, you will have no
difficulty in carrying each of the analyses you want.

 

[Mark McDonough]

Thanks Roger but I believe my data is in order. See below for a small
portion

Origin Port MTH/YR Maersk MEDIT NYK LINE OOCL
Hong Kong MAY2006 80 45 4 120
Hong Kong MAY2006 80 45 120
Hong Kong MAY2006 80 45 120
Hong Kong MAY2006 80 45 120
Hong Kong JUN2006 80 55 115
Hong Kong JUN2006 80 55 115
Hong Kong JUN2006 80 55 115
Hong Kong JUN2006 80 55 115
Hong Kong JUL2006 80 55 90
Hong Kong JUL2006 80 55 90


The result I need is to have a report with the suppliers down the left and
the months across the top. I need to be able to select a supplier and see
what their volume is by month.

Cheers
Mark.

 

[Roger Govier]

Hi Mark

No, the problem is that the Customers all have an individual column with
your layout, whereas for a Pivot Table to work effectively, there should
be one column (field) for Customer, with each row containing only data
relevant to that Customer.
The PT will do all of the adding to give a single value per Customer per
Month or per Port.

What you need is
Origin Port MTH/YR Quantity Customer
Hong Kong MAY2006 80 Maersk
Hong Kong MAY2006 45 MEDIT
Hong Kong MAY2006 4 NYK LINE
Hong Kong MAY2006 120 OOCL
Hong Kong MAY2006 80 Maersk
Hong Kong MAY2006 45 MEDIT
Hong Kong MAY2006 120 OOCL
Hong Kong MAY2006 80 Maersk
Hong Kong MAY2006 45 MEDIT
Hong Kong MAY2006 120 OOCL
etc.

 

[Herbert Seidenberg]

Here is an example of Roger's method that might help someone else.
Assume your data looks like this:
Date Port MAER MEDIT NYKL OOCL
06/01/06 Hong Kong 16 91
08/01/06 Jakarta 16 21 71
05/01/06 Hong Kong 29 83
05/01/06 Hong Kong 88 62 54
05/01/06 Jakarta 66 91 78 63
07/01/06 Singapore 74 46
06/01/06 Singapore 30 31
07/01/06 Jakarta 60 78 92
06/01/06 Hong Kong 55 89 91
06/01/06 Hong Kong 53 49
03/01/06 Jakarta 48 77 99
08/01/06 Singapore 28 89 51
03/01/06 Singapore 90 38
07/01/06 Hong Kong 23 69
04/01/06 Jakarta 30 67 71 36
05/01/06 Hong Kong 18 80 59 21
05/01/06 Singapore 82
07/01/06 Hong Kong 62 46 87
06/01/06 Hong Kong 30 89 72 59
05/01/06 Hong Kong 60 89
06/01/06 Jakarta 31 43 45
04/01/06 Singapore 68 17 97

Do John Walkenbach's reverse pivot table on the last 5 columns to get
this:

Sum of Value Total
Total 3548

Now switch the first and second column and do another reverse pivot
table
on the last 5 columns, with this result:

Count of Value Total
Total 60

Double click on the 3548 to get a 3 x 88 array.
Double click on the 60 to get another 3 x 88 array.
Combine the unique columns to get a 4 x 88 array.
Label the columns Date, Port, Carrier and Value.
Do a regular pivot table with these 4 columns.
Layout: Row = Carrier, Port; Column = Date; Data = Sum of Value
Options: Uncheck grand totals, hide totals
You should get this:

Sum of Value Date
Carrier Port 3/1/06 4/1/06 5/1/06 6/1/06 7/1/06 8/1/06
MAER Hong Kong 166 85 85
Jakarta 48 30 66 60 16
Singapore 68 30 28
MEDIT Hong Kong 171 247
Jakarta 67 91 31 21
Singapore 90 17 82 89
NYKL Hong Kong 231 163 46
Jakarta 77 71 78 43 78 71
Singapore 38 31 74 51
OOCL Hong Kong 75 199 156
Jakarta 99 36 63 45 92
Singapore 97 46

Carrier, Port and Date have dropdown lists that lets you select all
your options.

 

[Roger Govier]

Hi Herbert

Thank you for taking the trouble to give the detailed steps necessary to
achieve the end result.,
My quick and rather glib answer had totally failed to point out to Mark
the necessity for the "double pass" through John Walkenbach's method to
get both Date and Port split out.

This will be very helpful for Mark and others wishing to carry out the
same sort of exercise and I have put it amongst my "saves" to ensure
that I give a more complete response next time round.

 

[ilia]

Your data is not normal (suppliers should be broken out into rows, and
volume should be the only attribute), but here's what you can do.

Port - Page (so you can select)
Month - Row
Suppliers - put in Row area, all of them. Then, drag the entirety of
data into Column. Set calculation to Sum.

This should get you what you want.