Quote:
Originally Posted by Anne R
I am trying to create a count of products by location and diposition similar
to below
Location Aging Retain Aging Dispose
Boston 12, 13, 15(2) 20, 21(2)
Dallas 17(2), 20 10, 17(2)
Los Angeles 18, 19, 20 20(2)
New York 10, 15 10, 20
From raw data that looks like below however the values in Locatin and Aging
will vary each time.
Product ID Location Aging Disposition
0001 New York 10 Retain
0002 New York 10 Dispose
0003 New York 15 Retain
0004 New York 20 Dispose
0005 Boston 12 Retain
0006 Boston 13 Retain
0007 Boston 15 Retain
0008 Boston 15 Retain
0009 Boston 20 Dispose
0010 Boston 21 Dispose
0011 Boston 21 Dispose
0012 Dallas 10 Dispose
0013 Dallas 17 Retain
0014 Dallas 17 Retain
0015 Dallas 17 Dispose
0016 Dallas 17 Dispose
0017 Dallas 20 Retain
0028 Los Angeles 18 Retain
0029 Los Angeles 19 Retain
0030 Los Angeles 20 Retain
0031 Los Angeles 20 Dispose
0032 Los Angeles 20 Dispose
Have been trying to use GETPIVOTDATA and IDEX but can't figure out how to
make it work.
Thanks,
Anne
|
Anne
To use getpivotdata you first need to have a pivot table which you didn't mention if you had one. A pivot would be ideal in this situation and no formula would be required.
Create a pivot table on the raw data and arrange as necessary with page headers columns and rows however in the data area make sure you place the "Product ID" field. Then 'doubleclick' it and choose 'count' instead of 'sum'
The result is a count of 'product ID''s for whatever selections you have in your pivot
Similar Threads
Threaded Mode
