Pivot table for made and sent dates

**gozillasnack** · 08-17-2007, 01:57 PM

Hey guys,

I've been tackling this issue for a while. Basically my data source is some inventory data, which has two dates (in year) for when the product was made, which factory it was made in, and when it was sent out. So a typical entry would look like this:

ID#       Factory          DateMade         DateSent
415612  New York        2001                2004

I have a pivot table that illustrates the data from this data, and I want it to have the year, the count for the number of products made, and then the count for the products sent broken down by year and factory. So, something like this:

Year      Factory          Count of Made   Count of Sent
2001                          1923                1709
            New York        1521                1203
            New Jersey     402                  506

2002                          2201                2202
            New York        1699                1802
            New Jersey      502                 400

The problem is, I've set most of the table based on the "Year" being the year made, so when I try to do the Count o sent, I can only look up what was made in the year. Anyone have a good idea for this? Thanks in advance.

**tuph** · 08-19-2007, 07:24 PM

I think I would use a second pivot table based on the year sent.

**gozillasnack** · 08-20-2007, 09:05 AM

Yeah, I've done that, and that's an OK solution

The problem is that the people who want this report is really picky about how it looks, and I've set the worksheet to auto-group the factory location when the pivot table is updated, so that the user can group all of the factory locations, thus not showing it, and when he/she needs to see it, he/she can simply expand the group to look at it. So, if I was going to have another table, I would need to have it get aligned so that the regions for both tables are grouped. Thus I would need the year and factory location to be aligned. The problem is, the year made starts at 1997 while the year sent starts at 1998, so there's already a misalignment there. Let alone that some factories only make and not send units.