Return all products from a customer on a date, multiple values with multiple criteria

Copper Contributor

I am running a report where I want to know all the products a customer bought on the day that they purchased something specific. For example, I have a list of all time purchases but I want to know, when a customer bought a pack of cigarettes, what else did they buy that day?


I have a list of the customers who bought cigarettes and they day they bought them. Now I need a way to pull a list of all other products they bought that day using their account ID and the date I have. Here is a screenshot of the example sheet I have:

bundle example1.png

It is important that I use the account ID in column B as the reference along with the date, because some customers share account names on the actual report. Now here is the example of raw data I have, which is on a separate sheet:

bundle example2.png

In this example, for Customer 1,  I need a way to pull the products, "Milk, Bread, Oranges, Almonds" into the products column. I'm not sure if there's a way to keep it all in one cell, or if I can have it enter multiple products in cells to the right? Or maybe there's a better way to do this entirely, I'm open to suggestions. Thank you for your help!

5 Replies


In D2:

=TEXTJOIN(", ", "", FILTER('all raw data 2'!$C$2:$C$1000, ('all raw data'!$B$2:$B$1000=B2)*('all raw data 2'!$E$2:$E$1000=C2), ""))

best response confirmed by sarahprkr23 (Copper Contributor)
Oh this works great actually, don't know why I didn't think to make a pivot table! thank you!


To play bit more with PivotTable


1 best response

Accepted Solutions
best response confirmed by sarahprkr23 (Copper Contributor)