I have a large database consisting of about 40 columns and several hundred
rows. The headers of the columns denote the stages of a production project:
let's say, "To Design," "To Production," "To Marketing," "To Distribution,"
etc.

The row heads identify the many components that go through the various
stages of the project (let's say "Thingie," "Thingie 2," "Thingie 3," etc.).
There are many, many thingies, and they all go through the stages of
production represented by the columns at different times. The body of the
database consists of the bazillions of dates at which each part reaches each
stage of production.

My question is: Is it possible to construct a pivot table from this database
that will show all of the dates down one side (either the top or the bottom,
it doesn't matter) and identify what parts are where on those dates? For
example, instead of looking through the database for all occurrences of
August 2nd to determine how much is going on on that day, I'd like to have
August 2nd appear along an axis of the pivot table and show what's going on:
"Thingie 2 to Marketing," "Thingie 59 to Production," "Thingie 107 to
Distribution," etc. Essentially this means turning the original database
inside out, so that the dates appear in order and the row and column heads
concatenater as data points inside the table.

I've tried using filters of various sorts on the table to simplify my work,
but it just seems like there should be some sort of way to explode the
database and reassemble it in the way I've described. Can a pivot table do
this? Is this even possible? I've dragged fields this way and that but can't
get close to what I need. Am I doing too much crack?

Please help!

To reorganize the data, you can use the 'unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

Then, create a pivot table from the restructured data, with Date,
Project Stage and Component in the Row area, and another copy of
Component in the data area, as Count of Component.

Turn off the subtotals, and you'll have a list of tasks by date.


PBJ wrote:
I have a large database consisting of about 40 columns and several hundred
rows. The headers of the columns denote the stages of a production project:
let's say, "To Design," "To Production," "To Marketing," "To Distribution,"
etc.

The row heads identify the many components that go through the various
stages of the project (let's say "Thingie," "Thingie 2," "Thingie 3," etc.).
There are many, many thingies, and they all go through the stages of
production represented by the columns at different times. The body of the
database consists of the bazillions of dates at which each part reaches each
stage of production.

My question is: Is it possible to construct a pivot table from this database
that will show all of the dates down one side (either the top or the bottom,
it doesn't matter) and identify what parts are where on those dates? For
example, instead of looking through the database for all occurrences of
August 2nd to determine how much is going on on that day, I'd like to have
August 2nd appear along an axis of the pivot table and show what's going on:
"Thingie 2 to Marketing," "Thingie 59 to Production," "Thingie 107 to
Distribution," etc. Essentially this means turning the original database
inside out, so that the dates appear in order and the row and column heads
concatenater as data points inside the table.

I've tried using filters of various sorts on the table to simplify my work,
but it just seems like there should be some sort of way to explode the
database and reassemble it in the way I've described. Can a pivot table do
this? Is this even possible? I've dragged fields this way and that but can't
get close to what I need. Am I doing too much crack?

Please help!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Wow! That does exactly what I need! Thank you so much for the help, Debra.
I've seen your posts before (and checked out Contextures, as well): always
wonderfully useful. Thanks again!

"Debra Dalgleish" wrote:

To reorganize the data, you can use the 'unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

Then, create a pivot table from the restructured data, with Date,
Project Stage and Component in the Row area, and another copy of
Component in the data area, as Count of Component.

Turn off the subtotals, and you'll have a list of tasks by date.


PBJ wrote:
I have a large database consisting of about 40 columns and several hundred
rows. The headers of the columns denote the stages of a production project:
let's say, "To Design," "To Production," "To Marketing," "To Distribution,"
etc.

The row heads identify the many components that go through the various
stages of the project (let's say "Thingie," "Thingie 2," "Thingie 3," etc.).
There are many, many thingies, and they all go through the stages of
production represented by the columns at different times. The body of the
database consists of the bazillions of dates at which each part reaches each
stage of production.

My question is: Is it possible to construct a pivot table from this database
that will show all of the dates down one side (either the top or the bottom,
it doesn't matter) and identify what parts are where on those dates? For
example, instead of looking through the database for all occurrences of
August 2nd to determine how much is going on on that day, I'd like to have
August 2nd appear along an axis of the pivot table and show what's going on:
"Thingie 2 to Marketing," "Thingie 59 to Production," "Thingie 107 to
Distribution," etc. Essentially this means turning the original database
inside out, so that the dates appear in order and the row and column heads
concatenater as data points inside the table.

I've tried using filters of various sorts on the table to simplify my work,
but it just seems like there should be some sort of way to explode the
database and reassemble it in the way I've described. Can a pivot table do
this? Is this even possible? I've dragged fields this way and that but can't
get close to what I need. Am I doing too much crack?

Please help!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

You're welcome! Thanks for letting me know that it helped.

PBJ wrote:
Wow! That does exactly what I need! Thank you so much for the help, Debra.
I've seen your posts before (and checked out Contextures, as well): always
wonderfully useful. Thanks again!

"Debra Dalgleish" wrote:


To reorganize the data, you can use the 'unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

Then, create a pivot table from the restructured data, with Date,
Project Stage and Component in the Row area, and another copy of
Component in the data area, as Count of Component.

Turn off the subtotals, and you'll have a list of tasks by date.


PBJ wrote:

I have a large database consisting of about 40 columns and several hundred
rows. The headers of the columns denote the stages of a production project:
let's say, "To Design," "To Production," "To Marketing," "To Distribution,"
etc.

The row heads identify the many components that go through the various
stages of the project (let's say "Thingie," "Thingie 2," "Thingie 3," etc.).
There are many, many thingies, and they all go through the stages of
production represented by the columns at different times. The body of the
database consists of the bazillions of dates at which each part reaches each
stage of production.

My question is: Is it possible to construct a pivot table from this database
that will show all of the dates down one side (either the top or the bottom,
it doesn't matter) and identify what parts are where on those dates? For
example, instead of looking through the database for all occurrences of
August 2nd to determine how much is going on on that day, I'd like to have
August 2nd appear along an axis of the pivot table and show what's going on:
"Thingie 2 to Marketing," "Thingie 59 to Production," "Thingie 107 to
Distribution," etc. Essentially this means turning the original database
inside out, so that the dates appear in order and the row and column heads
concatenater as data points inside the table.

I've tried using filters of various sorts on the table to simplify my work,
but it just seems like there should be some sort of way to explode the
database and reassemble it in the way I've described. Can a pivot table do
this? Is this even possible? I've dragged fields this way and that but can't
get close to what I need. Am I doing too much crack?

Please help!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html