SOLVED

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

Copper Contributor

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

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!

5 Replies

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

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)
Solution

Pivot Table:

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

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

Why cigarettes?

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

To play bit more with PivotTable

1 best response

Accepted Solutions
best response confirmed by sarahprkr23 (Copper Contributor)
Solution

Pivot Table: