0

Say I have a transactional list of every purchase made in the last month.

How can I show a list of the customers that have purchased both Product A and Product B.

I can make a pivot table and filter against the products, but it gives me an A or B list, not A and B.

How can I do this ?

Edit to add data sample and result I wish to see: (note that actual data is several thousand lines long)

Customer Name  |  Purchase Date   |  Product;
Bob            |  02/05/2018      |  Eggs;
Bob            |  02/05/2018      |  Cheese;
Ali            |  02/05/2018      |  Eggs;
Ali            |  02/05/2018      |  Brocoli;
Jon            |  02/05/2018      |  Eggs;
Jon            |  02/05/2018      |  Cheese;
Jon            |  02/05/2018      |  Brocoli;

Who purchased Eggs and Cheese between 01/05/2018 and 30/05/2018?

Customer Name   |
Bob
Jon
3
  • There will many ways to achieve this, but we will be able to best answer it if we can see a sample of data, and what you expect as an outcome
    – PeterH
    Aug 1, 2018 at 14:11
  • Thanks Peter! I tried adding data but not sure how to attached a table. It formatted it as a paragraph unfortunately and didnt keep my line breaks.
    – Sdieser
    Aug 1, 2018 at 14:25
  • for future posts, select your table when you enter your question and use Ctrl+K and it will format it as a table (sort of)
    – PeterH
    Aug 1, 2018 at 14:27

1 Answer 1

0

(Sorry cannot comment so have to post)

A quick fix, probably not very elegant, but does the job. Please see the layout here:

Layout and formula

I use two pivot tables to get the names that take Eggs and Cheeses, and then use a simple COUNTIF formula to calculate the number of appearances in these two pivot tables. If the calculated value is 2 then it meets your criteria.

I'm pretty sure there are better function methods and it's easy to write a macro for that. Let me now if you want more solutions.

1
  • Thanks Nicholas, this is good for the basic example I gave, but my ultimate goal is to try and get to a point where I can use something like a Slicer to select multiple products at a time, so I can do some cross-product marketing analysis. I want to understand which products are purchased most often together.
    – Sdieser
    Aug 1, 2018 at 23:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.