Pivot table- Select only items having two different variables

[Ecel Dumbo]

Hello,


Please refer the attached spreadsheet. The title of this post may be confusing. If you could go through the spreadsheet, i guess you will understand better.


I would like to show in a pivot table customers who have ordered two different products together only


Any help will be appreciated


http://speedy.sh/HjycK/Pivot-table-Query-Select-only-items-that-have-same-variables.xls


Regards,

Excel Dumbo

 

[Hui]

Ecel Dumbo


Whynot add an extra field to your Data Table

E1: Test

E2: =SUMPRODUCT(($B$2:$B$13=B2)*($A$2:$A$13={"AB","CD"}))>0

Copy E2 down


Then use Test as a Page Field in the PT

 

[Ecel Dumbo]

Thanks HUi for your time. May be I was not clear in my question


I need to pick up only those customers who have ordered products "AB" and CD" together. In this example- Karimbinal Corporate and Kuttan ordered only "CD" and it got selected. I need to ignore this because this cutomer did not order "AB"


If a customer has ordered products "AB" and "CD" , then it should get selected.


Please see attached sheet


http://speedy.sh/MgQzA/Pivot-table-Query-Select-only-items-that-have-same-variables-CHANDOO.xls

 

[bobhc]

Good day Ecel Dumbo


Why not just a table and the filter the product column on AB CD

 

[Ecel Dumbo]

Hi Bobhc, Like I said I would need to see in a pivot table all the customers who have ordered Products "AB" and "CD" together. THis is a sample table of a massive file and I am not sure how your method will work

 

[bobhc]

Ecel Dumbo


I did turn your data in to a table, including the headers and then filtered on products to get the result you are after

 

[NARAYANK991]

Hi ,


Modify Hui's formula slightly to :


=(--SUMPRODUCT(($B$2:$B$13=B2)*($A$2:$A$13="AB"))>0)+(--SUMPRODUCT(($B$2:$B$13=B2)*($A$2:$A$13="CD"))>0)


Include this Test field in your PT , and use it as a Report Filter ; you will have values such as 0 , 1 , 2 ; a value of 0 means the customer did not order either AB or CD ; a value of 1 means the customer ordered one of either AB or CD ; a value of 2 means the customer ordered both AB and CD.


Narayan

 

[Ecel Dumbo]

THanks Narayanan for your help.


Thanks to bobc and Hui too.