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

[sarahprkr23]

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:



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:



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!

[#2]

@sarahprkr23 

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), ""))

[#3 · Accepted solution]

@sarahprkr23 

Pivot Table:

[#4]

Oh this works great actually, don't know why I didn't think to make a pivot table! thank you!

[#5]

Why cigarettes?

[#6]

@sarahprkr23 

To play bit more with PivotTable