Pivot Table Help (Duplicates To Be Removed)

Posted by Tjong on November 16, 2001 3:19 AM

DESCRIPTION:

I am constructing a PivotTable showing text in Column A, and a $amount in column B (note: dollar amount can be positive or negative).

Within the data, some rows have the same text but different dollar amounts. For instance:

ROW 1 -> Column A: Canon ; Column B: $600
ROW 2 -> Column A: Canon ; Column B: -$600

I want to identify which rows have the same text value in column A, and identical (but negative/positive: i.e. $600 vs -$600) value in column B.

QUESTIONS:

i)Should I create a third column so that such rows could be flagged?
ii) What formula do I use to flag such rows?
iii) I assume this manipulation has to be done previous to the PivotTable being created, right?

Please help if you can! Thanks

Posted by Mark W. on November 16, 2001 6:34 AM

Suppose that cells A1:B5 contains...

{"Text","Amount"
;"Canon",600
;"Canon",-600
;"Nikon",500
;"Nikon",-400}

...Enter "Selector" into C1 and the formula,
=SUMIF($A$2:$A$5,A2,$B$2:$B$5)=0,into C2 and
copy down. Place the 'Selector' field button
in your PivotTable's PAGE area, and choose it's
TRUE item. Your PivotTable will now only list
'Text' items whose amounts net to zero.