Creating a list of products sold together with a selected product

[MJezierski1050]

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
1	A
2	A
2	B
2	C
3	A
3	B
4	A
4	B
4	C
4	D
5	B
5	D

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 products	Number of orders
Product B	3
Product C	2

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]

@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.

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.

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

[#3]

@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.