Items bought together

**MJB123** · 09-29-2015, 11:24 AM

Hi everyone.

I need some help with a formula which is related a previous thread (see link below).

http://www.excelforum.com/excel-gene...-data-set.html

I need a formula which counts the unique number of orders when two items are bought together. The formulas in the thread attached double counts some orders if an order contains more of the same item (hope that makes sense!).

So in my attached example, item F & A are bought together in 4 separate transactions, and i need a formula to calculate that.

Hope someone can help.

Thanks

Mike

**shg** · 09-29-2015, 11:48 AM

One way:

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1												A	A	A	A	A	B	B	B	B	C	C	C	D	D	E
2	Order	Item		Order	A	B	C	D	E	F		B	C	D	E	F	C	D	E	F	D	E	F	E	F	F
3	1	D		1	0	0	0	1	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	2	C		2	0	0	1	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	3	A		3	1	0	0	0	0	1		0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
6	3	F		4	0	1	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	4	B		5	0	0	1	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8	5	C		6	0	0	1	1	1	0		0	0	0	0	0	0	0	0	0	1	1	0	1	0	0
9	6	C		7	1	0	0	0	0	1		0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
10	6	D		8	0	0	0	1	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	6	E		9	0	0	0	0	0	1		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	7	A		10	0	1	0	0	1	0		0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
13	7	F		11	1	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	7	F		12	0	0	1	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	8	D		13	1	0	0	0	1	1		0	0	0	1	1	0	0	0	0	0	0	0	0	0	1
16	9	F		14	0	0	1	1	0	0		0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
17	10	B		15	0	0	1	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	10	E		16	1	0	0	0	1	0		0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
19	11	A		17	0	0	0	1	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	12	C		18	0	1	0	0	0	1		0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
21	13	A		19	1	0	0	0	1	1		0	0	0	1	1	0	0	0	0	0	0	0	0	0	1
22	13	E		20	0	0	0	1	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
23	13	F
24	14	C										0	0	0	3	4	0	0	1	1	2	1	0	1	0	2
25	14	D
26	15	C
27	16	A
28	16	E
29	17	D
30	18	B
31	18	F
32	19	A
33	19	A
34	19	E
35	19	F
36	20	D

E3 and across and down: =--(COUNTIFS($A$3:$A$36, $D3, $B$3:$B$36, E$2)>0)
L3 and across and down: =INDEX($E3:$J3, MATCH(L$1, $E$2:$J$2)) * INDEX($E3:$J3, MATCH(L$2, $E$2:$J$2))

**MJB123** · 09-29-2015, 12:05 PM

Hi shg. Thanks for your post.

I need to present it as a 2x2 item matrix as I am going to apply a heat map to it. I also have lots of items in reality and thousands of orders so i couldn't list out all the possible combinations along the top.

Thanks

Mike

**MJB123** · 09-30-2015, 04:38 AM

Anyone else got any ideas? thanks

**MJB123** · 10-01-2015, 07:52 AM

Hi shg - do you have any ideas of a formula which would provide the answers in a 2x2 matrix?

Mike

**laansesu** · 10-01-2015, 08:05 AM

Is it ok with this format?

**MJB123** · 10-01-2015, 08:10 AM

Hi laansesu. Yes that is the format I am after, I now just need the formula which calculates the figures i.e. the number of transactions where the two items have been bought together

Thanks

**laansesu** · 10-01-2015, 08:23 AM

Used Sumifs in the 2*2 matrix as you desired, you can check the same in the (previous post)attached excel

But we have to delete the duplicates based on column C concatenated data

**samba_ravi** · 10-01-2015, 08:27 AM

Will you show sample result for 1 row i,e E3:J3 and E3:E8

**laansesu** · 10-01-2015, 08:34 AM

Formula at F3 is =SUMIFS($A:$A,$B:$B,$E3,$B:$B,F$2) to Formula at K4 is =SUMIFS($A:$A,$B:$B,$E3,$B:$B,K$2)

to

Formula at F8 is =SUMIFS($A:$A,$B:$B,$E8,$B:$B,F$2)

Is this you required

**samba_ravi** · 10-01-2015, 08:40 AM

e3=IF($D3=E$2,SUMPRODUCT((COUNTIFS($B$2:$B$35,$D3,$A$2:$A$35,ROW(INDIRECT(MIN($A$2:$A$35)&":"&MAX($A$2:$A$35))))>=2)*1),SUMPRODUCT(((COUNTIFS($B$2:$B$35,$D3,$A$2:$A$35,ROW(INDIRECT(MIN($A$2:$A$35)&":"&MAX($A$2:$A$35))))>0)+(COUNTIFS($B$2:$B$35,E$2,$A$2:$A$35,ROW(INDIRECT(MIN($A$2:$A$35)&":"&MAX($A$2:$A$35))))>0)>=2)*1))

Please Login or Register  to view this content.

Try this and copy across

**MJB123** · 10-01-2015, 08:45 AM

Hi Laansesu

Do you have an example spreadsheet with the formula working? I can't seem to make those formulas return the result

Thanks

Mike

**MJB123** · 10-01-2015, 08:52 AM

Hi nflsales.

This formula looks great! Thanks for your help.

How could the formula be adapted if the order number was a number in text format between 15 and 20 digits long?

Thanks

**samba_ravi** · 10-01-2015, 08:57 AM

Get unique orders in separate sheet and give the range reference in the formula

**MJB123** · 10-01-2015, 08:58 AM

The real data I am using looks like this

110225215052815 Curtains
110225215052815 Curtains
110225225052818 Quilts + Pillows
110225235052819 Quilts + Pillows
110225235052819 Quilts + Pillows
110225245052820 Curtains
110225245052820 Curtains
110225245052820 Home Decor
110225245052820 Storage
110225245052820 Tracks & Poles
110225255052821 Quilts + Pillows
110225255052821 Quilts + Pillows
110225265052822 Lighting
110225275052823 Lighting

**MJB123** · 10-01-2015, 09:09 AM

Could you show me in this spreadsheet with the real data?

Items bought together

LinkBack

Thread Tools

Rate This Thread

Display

Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Re: Items bought together

Thread Information

Users Browsing this Thread

Similar Threads

Most frequent items bought together

giving a discount based on if a customer bought two items and his name came up twice

[SOLVED] Number of items bought between 11:00 and 11:59 for example

Create a list of bought-out items for various assemblies

[SOLVED] Pricing Lookup based on qty bought

total of different items bought per month

how do I set up time sheet and items bought expense sheet

Bookmarks

Bookmarks

Posting Permissions