I did try Dom's but I muffed up something and can't get it to work properly. Nonetheless, while it is some amazing formula-smithing I still think it would get really, really expensive in terms of calculation burdens. So here's the pivot thing -- manually. We get the first part done a understand that bit then we can see if a touch of VBA would make life easier.
Our sample data is as posted
here but with column A having a header
Store and column B having a header
Prod ID.
Were this sans the combination bit, this would be a very simple pivot table. Just drag
Store in as a row field and
Prod ID as both the column field and the data field. By default we would get:
Post topic 231459.xls |
---|
|
---|
| A | B | C | D | E |
---|
3 | Count of Prod ID | Prod ID | | | |
---|
4 | Store | 12345 | 12346 | 27142 | 27143 |
---|
5 | 210 | | | 1 | 1 |
---|
6 | 211 | 1 | 1 | 1 | |
---|
7 | 212 | | | 1 | 1 |
---|
8 | 213 | 1 | 1 | 1 | |
---|
9 | 214 | 1 | 1 | 1 | |
---|
10 | Grand Total | 3 | 3 | 5 | 2 |
---|
|
---|
But we need calculated items. If we try to create calculated items with that pivot table, Excel is gonna whine at us:
So we need to create a dummy field. All we do is take column C, add in a header
Dummy and fill the column with 1's.<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" align="left"><tr valign="top" style="white-space:nowrap;"><th width="63" height="21" valign="bottom"><font face="Monospace" size="1">Store</font></th><th width="63" height="21" valign="bottom"><font face="Monospace" size="1">Prod ID</font></th><th width="63" height="21" valign="bottom"><font face="Monospace" size="1">Dummy</font></th></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">210</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">27142 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">210</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">27143 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">211</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">12345 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">211</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">12346 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">211</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">27142 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">212</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">27142 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">212</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">27143 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">213</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">12345 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">213</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">12346 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">213</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">27142 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">214</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">12345 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">214</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">12346 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr><tr valign="top" style="white-space:nowrap;"><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">214</font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">27142 </font></td><td width="63" height="21" align="right" valign="bottom"><font face="Monospace" size="1">1</font></td></tr></table>
You might need to use the wizard and backup to the 2<sup>nd</sup> setup step and re-define the used area to include column C now. So we go back to our pivot, drag
Prod ID out of the data area (grab cell A3 and drag it off the pivot to do this). Now drag
dummy into the data area and you get this:
Post topic 231459.xls |
---|
|
---|
| A | B | C | D | E |
---|
3 | Sum of Dummy | Prod ID | | | |
---|
4 | Store | 12345 | 12346 | 27142 | 27143 |
---|
5 | 210 | | | 1 | 1 |
---|
6 | 211 | 1 | 1 | 1 | |
---|
7 | 212 | | | 1 | 1 |
---|
8 | 213 | 1 | 1 | 1 | |
---|
9 | 214 | 1 | 1 | 1 | |
---|
10 | Grand Total | 3 | 3 | 5 | 2 |
---|
|
---|
Select a cell with a product ID in it in the pivot, i.e. a cell in B4:E4 and then click on the pivot table toolbar to pull up the
Formulas > | Calculated Item... menu option.
You should now see the
Insert Calculated Item in "Prod ID" dialog box.
Type
Combo1 in the
Name box and then
=and( in the
Formula box. If
Prod ID is not selected in the
Fields list, select it. Then you can pick
'12345' from the
Items list and click the
Insert Item button and then in the
formula box add in
>0, and then add the next item and so forth until you build the formula to be
=AND('12345' >0,'12346' >0,'27142' >0)
Repeat the process to define a second calculated item:
Combo2
Click the OK button at the bottom and you should have what I posted earlier.