Extracting Data that meets multiple criteria in one column for one customer ID

[MichiganWilliams]

I swear this has to be easy but I'm not figuring this out. I've looked at VLOOKUP, Index & Match, Pivot Tables, etc.

I've got a table with 65k rows. In one column, I have a customerID, in another column I have an item purchased. I have many other columns, but I want to be able to find customers that have purchased BOTH item A & item B, doesn't have to be on the same date. When I try filtering my table, I get anyone who purchased either A or B.

I tried a Pivot table and I did Rows Customer ID and column product purchased, I think I threw in another random field in the values column set to Count. It's not exactly what I want though. With 65k rows, that takes a lot of scrolling.
Imagine something like the table below, but with a ton of rows. I'd want it to return only Customer 1 because they've purchased A and they've purchased B. Is there a formula that could extract data like this when it's dependent on a customer ID in one column and multiple criteria in a separate column?

Follow up question, What if I wanted someone who has purchased BOTH A and any other product?
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer ID[/TD]
[TD]Product Purchased[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]1/1/19[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]1/1/19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]1/1/19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]1/1/19[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]2/1/19[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[TD]2/1/19[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]E[/TD]
[TD]2/1/19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]2/1/19[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Customer ID[/td][td=bgcolor:#5B9BD5]Product Purchased[/td][td=bgcolor:#5B9BD5]Date[/td][td][/td][td=bgcolor:#70AD47]Customer ID[/td][td=bgcolor:#70AD47]Product Purchased[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]

01/01/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]A,B[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]B[/td][td]

01/01/2019​

[/td][td][/td][td]

2​

[/td][td]B[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]C[/td][td=bgcolor:#DDEBF7]

01/01/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]C[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]A[/td][td]

01/01/2019​

[/td][td][/td][td]

4​

[/td][td]A,A[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7]

02/01/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]D,E[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

5​

[/td][td]D[/td][td]

02/01/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

5​

[/td][td=bgcolor:#DDEBF7]E[/td][td=bgcolor:#DDEBF7]

02/01/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]A[/td][td]

02/01/2019​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[MichiganWilliams]

I could work with that. What's the best way to get my table to do that?

 

[sandy666]

with PowerQuery aka Get&Transform and M-code below:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Customer ID"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Product Purchased", each Table.Column([Count],"Product Purchased")),
    Extract = Table.TransformColumns(List, {"Product Purchased", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extract[/SIZE]

 

[MichiganWilliams]

Thank you very much!

 

[sandy666]

You are welcome

Have a nice day