Producing a recommended items list

[bdr200]

Hi,
I have a large flat table containing columns for order number and product code. The aim is to produce a report that will enable me to identify collective product purchase trends so that it will be possible to recommend associated products when a customer selects a particular item. You may have experienced sellers such as Amazon doing this, e.g. "customers who bought 'A' also bought 'B' and 'C'".

I can do this with a series of tables using formulae but due to the high quantity of rows it is very slow. I could also do it with VBA but i have been asked to avoid this is possible. My question is can anyone think of a way of achieving this result using an Excel pivottable?

Many thanks.



Brian

 

[moonfish]

What you're trying to do requires vast amounts of calculation. With n products, you'll need at least n*(n-1)/2 calculations to establish the relations between the different products

Even if you want to use a pivot table, you need to organize your source data to reflect these relations, which requires the same amount of time.

I'd recommend making an n*n matrix to keep track of purchases, similar to this one:

Excel Workbook
	A	B	C	D	E
1	*	product5	product4	product3	product2
2	product1	*	*	*	*
3	product2	*	*	*	*
4	product3	*	*	*	*
5	product4	*	*	*	*
Sheet2

Fill it out with your data history and then update it with every future purchase.

 

[bdr200]

Hi,

Thanks for the quick response. I suspect your analysis is correct and your recommendation is the way forward if we are to keep the solution as an Excel VBA free spreadsheet.

I am going to suggest the problem is best handled either in SQL server via a clever query or within Excel by using VBA arrays to process the data quickly and output the results to a new table that can then be used as a database.

Once again, many thanks for your input.


Regards


Brian

 

[moonfish]

If you are considering options that don't use Excel I would recommend integrating this into your product database.

If you want to visualize the required number of calculations, try it with the following images: http://en.wikipedia.org/wiki/Complete_graph

Speaking of graphs, this page lists quite a few graph databases. It could be that one of them can be tailored to your needs with relative ease: http://en.wikipedia.org/wiki/Graph_database