Eliminate 0 values from pivot table

[mogens]

I have a stock detail sheet where I have a line for all stock
transactions. Basically they are organized as SKU (stock codes), LOT
(lot number) and UNITS (+/- incoming/outgoing). This sheet is summarized
in a pivot table.

The problem now is than when a certain SKU/LOT is used up, for instance
as +10 UNITS and 2 lines showing a usage of a total of -10 UNITS, I do
not want this SKU/LOT to occur in the pivot table.

Therefore I have considered to add a column that for each line shows for
instance an "E" for all lines where the stock as SKU/LOT is empty. Then
I could omit these rows from the pivot table to avoid these to be
displayed as "0" lines.

But can't figure out how to do this :-( Tried a sumproduct, byt this did
not succeed.

Hope for some help. Thanks!

Mogens

 

[steven1001]

for data with the StockCode in column A and the Qty in Column B and data
in rows 2 to 5, the following formula in C2 to C5 will return a label
value that can be selected in the pivot table to exclude the items
netting to zero (which will have a value of "don't show").

=IF(SUMIF($A$2:$A$5,$A$2:$A$5,$B$2:$B$5)=0,"don’t show","show")

regards..