Forum Discussion

AFOfficino's avatar
AFOfficino
Copper Contributor
Jan 28, 2022

Identify orders containing certain products

Hi, i have a list of Orders and related items.   I would like to identify in an easy way only orders containing items A-B-Z (at the same time). So if there's an order containing just A-B should no...
excel
Formulas and Functions
bosinander's avatar
bosinander
Iron Contributor
Jan 28, 2022

Hi AFOfficino 

Using UNIQUE, FILTER and TEXTJOIN will do it;

 

D2. Get the unique order numbers

=UNIQUE(A2:A47)

 

E2 and fill down; join the items to a searchable text

=TEXTJOIN("-";;SORT(FILTER(B1:B47;A1:A47=D2)))

 

Finally, G" filters the order numbers that matches the wanted items.

=FILTER(D2#;OFFSET(D2#;0;1)=G1)

Resources