PivotTable: formula for added & removed

[Krystal Peters]

Everymonth I get a list of office codes, and I need to compare if there are
any changes (additions or deletions).

Here is how the raw data comes in:

Group Office Main Office Codes Date
XBA MOFC 104 104 Aug-09
XBA MOFC 104 133 Mar-10

Then I create a pivottable where Group & Office are a page
then Main & Office Codes are rows with Date and a column
and the Count of Office Codes is the data.

This way the months are side by side and I can visually see
if there is a new code or old code removed. I want to go one step
future and instead of doing it visually have a formula (or two) that tells
me if a code has been added or removed.

Thanks, Krystal

 

[Roger Govier]

Hi Krystal

Whilst I love Pivot Tables, I don't see what they add here.
On the sheet with your raw data, in cell F2 enter
=IF(A2&"|"&B2&"|"&C2<>A2&"|"&B2&"|"&D2,"Changed","")
and copy down as far as required