Identify orders containing certain products

%3CLINGO-SUB%20id%3D%22lingo-sub-3081329%22%20slang%3D%22en-US%22%3EIdentify%20orders%20containing%20certain%20products%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3081329%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Ei%20have%20a%20list%20of%20%3CSTRONG%3EOrders%3C%2FSTRONG%3E%20and%20related%20%3CSTRONG%3Eitems%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20identify%20in%20an%20easy%20way%20%3CSTRONG%3Eonly%20orders%3C%2FSTRONG%3E%20containing%20items%20A-B-Z%20(at%20the%20same%20time).%20So%20if%20there's%20an%20order%20containing%20just%20A-B%20should%20not%20be%20identified.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20this%20can%20be%20done%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Immagine%202022-01-28%20180209.png%22%20style%3D%22width%3A%20362px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343380i96302CDAB1263C90%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Immagine%202022-01-28%20180209.png%22%20alt%3D%22Immagine%202022-01-28%20180209.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3081329%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3081616%22%20slang%3D%22en-US%22%3ERe%3A%20Identify%20orders%20containing%20certain%20products%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3081616%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1290222%22%20target%3D%22_blank%22%3E%40AFOfficino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20can%20be%20done%20with%20Power%20Query%20and%20then%20filter%20according%20to%20your%20requirement.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3081929%22%20slang%3D%22en-US%22%3ERe%3A%20Identify%20orders%20containing%20certain%20products%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3081929%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1290222%22%20target%3D%22_blank%22%3E%40AFOfficino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20Power%20Query%20option%20in%20attachment%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343404i501A641A6A5C779E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot.png%22%20alt%3D%22Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3081521%22%20slang%3D%22en-US%22%3ERe%3A%20Identify%20orders%20containing%20certain%20products%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3081521%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1290222%22%20target%3D%22_blank%22%3E%40AFOfficino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20UNIQUE%2C%20FILTER%20and%20TEXTJOIN%20will%20do%20it%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bosinander_0-1643390472259.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343387i96ACF417AE5C1E52%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bosinander_0-1643390472259.png%22%20alt%3D%22bosinander_0-1643390472259.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ED2.%20Get%20the%20unique%20order%20numbers%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DUNIQUE(A2%3AA47)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EE2%20and%20fill%20down%3B%20join%20the%20items%20to%20a%20searchable%20text%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DTEXTJOIN(%22-%22%3B%3BSORT(FILTER(B1%3AB47%3BA1%3AA47%3DD2)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%2C%20G%22%20filters%20the%20order%20numbers%20that%20matches%20the%20wanted%20items.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTER(D2%23%3BOFFSET(D2%23%3B0%3B1)%3DG1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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 not be identified.

 

How this can be done?

 

Immagine 2022-01-28 180209.png

3 Replies

Hi @AFOfficino 

Using UNIQUE, FILTER and TEXTJOIN will do it;

 

bosinander_0-1643390472259.png

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)

@AFOfficino 

This can be done with Power Query and then filter according to your requirement.

Hi @AFOfficino 

 

Another Power Query option in attachment

 

Screenshot.png