Creating a list of products sold together with a selected product

%3CLINGO-SUB%20id%3D%22lingo-sub-1962983%22%20slang%3D%22en-US%22%3ECreating%20a%20list%20of%20products%20sold%20together%20with%20a%20selected%20product%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1962983%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20dataset%20which%20includes%20i.a.%20orders%20numbers%20and%20names%20of%20products.%20Let's%20assume%20it%20looks%20like%20this%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EOrder%20No.%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EProduct%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EB%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EC%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EB%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EB%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EC%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3ED%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EB%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3ED%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20reality%2C%20there%20are%20several%20thousand%20distinct%20products%20and%20tens%20of%20thousands%20orders.%3CBR%20%2F%3EWhat%20I%20need%20to%20do%20is%20to%20create%20a%20possibly%20simple%20way%20to%20select%20a%20product%20from%20a%20list%20of%20products%20(or%20from%20a%20slicer)%20and%20see%20what%20other%20products%20accompany%20it%20most%20often.%3CBR%20%2F%3ESo%20regarding%20the%20example%20above%20-%20when%20I%20choose%20%22Product%20A%22%20from%20the%20list%2C%20I%20need%20to%20see%20that%20it%20was%20included%20in%204%20orders%2C%20and%20Product%20B%20also%20was%20in%20three%20of%20them%20(doesn't%20matter%20in%20which%20ones)%2C%20Product%20C%20in%20two%20of%20them%20etc.%20The%20same%20goes%20also%20for%20other%20products.%20Something%20like%20that%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSTRONG%3EProduct%3A%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSTRONG%3EProduct%20A%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSTRONG%3EOther%20products%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSTRONG%3ENumber%20of%20orders%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3EProduct%20B%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3EProduct%20C%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CBR%20%2F%3EIt%20seems%20quite%20simple%20and%20doable%20to%20me%20when%20I%20think%20of%20it%2C%20but%20whenever%20I%20start%20working%20on%20it%20and%20trying%20to%20prepare%20a%20datasource%20for%20the%20final%20pivot%20table%2C%20I%20end%20up%20in%20a%20dead-end.%20I%20thought%20about%20using%20pivot%20tables%20based%20on%20pivot%20tables%2C%20creating%20some%20arrays%20etc%2C%20but%20then%20my%20idea%20gets%20blocked%20and%20I'm%20not%20sure%20how%20to%20go%20about%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1962983%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-1964291%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20list%20of%20products%20sold%20together%20with%20a%20selected%20product%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1964291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893513%22%20target%3D%22_blank%22%3E%40MJezierski1050%3C%2FA%3E%26nbsp%3BHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20achieve%20your%20desired%20result%20by%20applying%20combination%20of%20formulas%20%26amp%3B%20pivot%20table.%26nbsp%3BBy%20selecting%20any%20product%20in%20cell%20'B1'%2C%20all%20associated%20products%20along%20with%20order%20no.%20and%20main%20product%20will%20appear%20as%20below%20table.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tauqeeracma_0-1607373320018.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238459i737ECCA5AD7EA1AE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22tauqeeracma_0-1607373320018.png%22%20alt%3D%22tauqeeracma_0-1607373320018.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESince%20pivot%20option%20is%20used%2C%20you%20need%20to%20refresh%20data%20after%20every%20selection.%3C%2FP%3E%3CP%3EAlso%20ensure%20that%20only%20'blank'%20value%20is%20unchecked%20and%20rest%20of%20the%20values%20(order%20no.)%20are%20always%20marked%20as%20checked.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tauqeeracma_1-1607373351828.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238460iFEA53786CD2B6020%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22tauqeeracma_1-1607373351828.png%22%20alt%3D%22tauqeeracma_1-1607373351828.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EA%20sample%20file%20is%20also%20attached%20for%20your%20reference%2C%20hope%20it%20will%20help%20you.%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20it%20works%20for%20you.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,

I have a dataset which includes i.a. orders numbers and names of products. Let's assume it looks like this:

Order No.Product
1A
2A
2B
2C
3A
3B
4A
4B
4C
4D
5B
5D

 

In reality, there are several thousand distinct products and tens of thousands orders.
What I need to do is to create a possibly simple way to select a product from a list of products (or from a slicer) and see what other products accompany it most often.
So regarding the example above - when I choose "Product A" from the list, I need to see that it was included in 4 orders, and Product B also was in three of them (doesn't matter in which ones), Product C in two of them etc. The same goes also for other products. Something like that:

 

Product:Product A
  
Other productsNumber of orders
Product B3
Product C2


It seems quite simple and doable to me when I think of it, but whenever I start working on it and trying to prepare a datasource for the final pivot table, I end up in a dead-end. I thought about using pivot tables based on pivot tables, creating some arrays etc, but then my idea gets blocked and I'm not sure how to go about it.

 

Any ideas?

2 Replies

@MJezierski1050 Hi,

 

You may achieve your desired result by applying combination of formulas & pivot table. By selecting any product in cell 'B1', all associated products along with order no. and main product will appear as below table.

tauqeeracma_0-1607373320018.png

Since pivot option is used, you need to refresh data after every selection.

Also ensure that only 'blank' value is unchecked and rest of the values (order no.) are always marked as checked.

tauqeeracma_1-1607373351828.png

A sample file is also attached for your reference, hope it will help you.

Please let me know if it works for you.

Thanks

Tauqeer

@MJezierski1050 

The previous reply could be enhanced by updating the pivot table automatically using VBA. See the attached version.

I have also attached a different, VBA-only solution. It will probably be slow with a large data file.