Find Inactive

[Krish]

I have a spreadsheet with two worksheets. The first worksheet is a Pivot table with Rows "CustomerID", Column "SalesRep" and the data is Sales for 2006. The second spreadsheet is Customer Master file with "CustomerID" and "SalesRep". I want to find out which customer did not buy during 2006. I am looking for a formula to produce minimal data. The Customer Master is 3000 rows, while the Pivot table has only 932 rows. Any help is truly appreciated.

Krish

 

[joytickle]

use advanced filter, set the criteria to sales date zero.
"Krish дµÀ£º
"

 

[Debra Dalgleish]

You could add a formula in a column on the Customer Master sheet.

For example, with customer names in column B:

=IF(COUNTIF('PivotSheet'!C:C,A2),"","Inactive")

would check for matching customer names in the pivot table (column C)

 

[Krish]

Thank you so much Debra. Your formula helped me to produce the results I needed to see.
You could add a formula in a column on the Customer Master sheet.

For example, with customer names in column B:

=IF(COUNTIF('PivotSheet'!C:C,A2),"","Inactive")

would check for matching customer names in the pivot table (column C)

 

[Debra Dalgleish]

You're welcome. Thanks for letting me know that it helped.