| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Inactive
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find Inactive
Krish,
One way to do it is to add a column to your master data, here I put it in column C and in cell c2 =IF(countif(Pivot!A:A,A2)0,"Sales","No Sales") and copy it down all the master record rows put a title in the c1 as "Sales" then use auto filter and select 'No Sales' for column c. -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Krish" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find Inactive
use advanced filter, set the criteria to sales date zero.
"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 ------=_NextPart_000_008C_01C713FD.35636AF0 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Google-AttachSize: 915 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" <HTML<HEAD <META http-equiv=Content-Type content="text/html; charset=iso-8859-1" <META content="MSHTML 6.00.3790.2759" name=GENERATOR <STYLE</STYLE </HEAD <BODY bgColor=#ffffff <DIV<FONT face=Arial size=2I 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.</FONT</DIV <DIV<FONT face=Arial size=2</FONT </DIV <DIV<FONT face=Arial size=2Krish</FONT</DIV</BODY</HTML ------=_NextPart_000_008C_01C713FD.35636AF0-- |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find Inactive
Another way is to do it at source.
In your source data add another column and call it 2006 Sales Assuming your data has dates in col A and customer in column B, then in your new column use a formulas such as:- =SUMPRODUCT(--($B$2:$B$1000=B2),--(YEAR($A$2:$A$1000)=2006))0 and copy down. This will give you a TRUE/FALSE against each record as to whether or not they had a purchase in 2006. Now simply drag that field into the page fields and select FALSE. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------- It's easier to beg forgiveness than ask permission :-) "Krish" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find Inactive
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 wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find Inactive
Thank you so much Debra. Your formula helped me to produce the results I needed to see.
"Debra Dalgleish" wrote in message ... 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 wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find Inactive
You're welcome. Thanks for letting me know that it helped.
Krish wrote: Thank you so much Debra. Your formula helped me to produce the results I needed to see. "Debra Dalgleish" wrote in message ... 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 wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find minimum of range based on multiple criteria | Excel Worksheet Functions | |||
| Find Function | Excel Discussion (Misc queries) | |||
| Find and Replace | Excel Worksheet Functions | |||
| Code needed to find records from bottom up | Excel Discussion (Misc queries) | |||
| find a cells from a range of cell | Excel Worksheet Functions | |||
Linear Mode
