A Pivot Table challenge--I can't do it, can you?

[LawrenceHG]

I need some help getting a particular result in a Pivot Table.

I have an Excel list of 19,000 purchase orders. Included in each purchase
order record (row) is the vendor name as well as the department and division
of the company that placed the purchase order. (Each division is made up of
numerous departments.)

I'm trying to answer the following question: How many departments in each
Division used a particular vendor? Please note that I'm *not* looking for the
number of orders placed with each vendor--I'm looking for the number of
departments that placed at least one order.

This is best illustrated with an example. Imagine this set of data. (If this
is hard to read, copy to a text editor with fixed-width font--my apologies.)

Vendor Department Division
Alpha Sales Asia
Alpha Sales Asia
Alpha Finance Asia
Alpha Finance Asia
Alpha Finance Asia
Alpha Finance Europe
Beta Sales Asia
Beta Mfg Europe

I want to produce the following Pivot Table

Sales Finance Asia Div Tot Finance Mfg Europe Div
Tot Grand Total
Alpha 1 1 2 1 1
3
Beta 1 1 1 1
2

I cannot figure out how to do this in a Pivot Table.

If I set the department as the data field of the Pivot Table and use the
count function, I get the number of purchase orders each department places.
Not what I want. If I use a function that gives me "1"s in the data cells
(e.g., adding a field of "1"s to the database and using the "Max" function to
aggregate data), then the PivotTable won't use the Sum function to create the
subtotals and Grand Total (I get "1"s there as well).

In a Pivot Table, is it possible to use a different function to total the
rows than is used to produce the individual values in the rows?

Can anyone suggest a Pivot Table method that will produce the results I
seek? Outside the Pivot Table world, I think I can do it with repeated
applications of the SUBTOTAL command, but it would take too long to execute
SUBTOTALS with 19,000 records.

Any help is appreciated.

 

[Roger Govier]

Hi Lawrence

You will need to add another column to your source table Named Count
In D2 enter
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=$B2)*($C$2:$C2=C2))=1,1,0)
and copy down

Add the Count Column to the source range for your PT, and use Sum of Count
as your data field

 

[Herbert Seidenberg]

Taking into account that different POs
can have the same Vendor, Dept, and Div...
http://www.freefilehosting.net/download/3ej3g

 

[LawrenceHG]

Roger--

All hail thee king of the MVPs! It works.

Very clever...I've seen this type of formula before, but I hadn't added it
to my bag of tricks...perhaps now I will.

I'm having trouble getting it to work on all 19,000 rows...the calculation
time is immense, but I verified it on a subset of the data.

Thanks for your help. I've really learned something good.

 

[Charles Williams]

Hi Lawrence,

If you can sort the data by Division then Department then Vendor you can add
a faster formula than sumproduct:
assuming vendor is in column D and the data starts in column 2 then add a
Counter column in E2
=IF(D1=d2,0,1)
and copy down for the 15000 rows.
Then just add the counter column to the pivot: SUM will give you the answer.

regards
Charles Williams
Decision Models

Roger--

All hail thee king of the MVPs! It works.

Very clever...I've seen this type of formula before, but I hadn't added it
to my bag of tricks...perhaps now I will.

I'm having trouble getting it to work on all 19,000 rows...the calculation
time is immense, but I verified it on a subset of the data.

Thanks for your help. I've really learned something good.

 

[LawrenceHG]

Hi Charles--

Thanks for the tip. Sorting the rows as you say clearly enables you to
simplify the formula. I'll try it out.

By the way, I don't know if you remember me, but I purchased a copy of
FastExcel a couple years ago and gave you suggestions for improving the Users
Guide.

Keep up the good work.

Lawrence