Association Rule mining using excel; Count how many rows have two searched for numbers

[DoctorofMadness]

I'm a college student working on a report currently and am having an issue with a problem I've recently run into and it's really throwing me for a loop. I thought I found a solution in this post, but couldn't get it to output anything but 0.
[[ Count how many times in total two values appear together in a row for all the rows ]]

To begin the project I was given a dataset of a little under 18000 transactions, with 19 different items that may have been purchased. The goal of the project is to draw what item types have associations with each other; for instance, if someone buys product 17, what is the confidence we can have that they would also purchase product 4 in the same transaction. The way I have the sheet looks as follows (first 20 transactions)

Transaction_ID	Customer_ID	Item_Types	Item Types
1​	15107144​	17,8,5,4,	17	8	5	4
2​	15107169​	17,9,8,	17	9	8
3​	15120097​	13,8,	13	8
4​	15128454​	9,9,8,	9	9	8
5​	15128488​	10,10,7,	10	10	7
6​	15131912​	2,	2
7​	15134734​	3,2,2,	3	2	2
8​	15500173​	13,10,8,7,5,2,2,1,	13	10	8	7	5	2	2	1
9​	15502484​	5,	5
10​	15507087​	7,	7
11​	15508887​	8,	8
12​	15510149​	12,	12
13​	15513135​	12,	12
14​	15514612​	13,	13
15​	15518225​	13,10,1,	13	10	1
16​	15518985​	8,8,3,	8	8	3
17​	15520494​	13,5,	13	5
18​	15523811​	17,	17
19​	15524504​	14,11,10,5,5,2,1,	14	11	10	5	5	2	1
20​	15529982​	12,	12

On the right side of the spreadsheet, the data is just pulled out of the Item_Types column using Textsplit() to remove the commas.

I thought I was onto something with the thread I linked above but cannot get it to output anything except 0, but I also have two theories onto why it doesn't work; each transaction might see the same item precedent or antecedent ( first item or second item ) more than once (see Transaction_ID 19, #5 appears twice).

I also decided to write the formula with 100 columns in the mmult() part, since I thought the large amount of blank space wouldn't affect anything since the way I am interpreting it to work.

=SUM((MMULT(--('Transaction IDs + Item Types'!D2:CY17918=L3),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})>0)
*(MMULT(--('Transaction IDs + Item Types'!D2:CY17918=O3),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})>0))


Let me know if you have any ideas. I am trying to figure a way to write a formula that will check if a row contains two values I am looking for anywhere in the row, while allowing the same number(s) to potentially appear more than once. I also know there are different ways this could be done, one of which using powerpivot, others using RStudio, I just haven't used those tools before and would rather try and do it on software I understand before resorting to learning new tools.

 

[duggie33]

If you want to return the transaction numbers for two matches you could do something like the following:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Transaction_ID	Customer_ID	Item_Types		Match 1	Match 2	Transaction Numbers
2	1	15107144	17,8,5,17,4,		10	1	8	15	19
3	2	15107169	17,9,8,17,		5	8	1	8
4	3	15120097	13,8,		10	13	8	15
5	4	15128454	9,9,8,		1	2	8	19
6	5	15128488	10,10,7,
7	6	15131912	2,
8	7	15134734	3,2,2,
9	8	15500173	13,10,8,7,5,2,2,1,
10	9	15502484	5,
11	10	15507087	7,
12	11	15508887	8,
13	12	15510149	12,
14	13	15513135	12,
15	14	15514612	13,
16	15	15518225	13,10,1,
17	16	15518985	8,8,3,
18	17	15520494	13,5,
19	18	15523811	17,
20	19	15524504	14,11,10,5,5,2,1,
21	20	15529982	12,
Sheet1

Cell Formulas
Range	Formula
G2:I2,G3:H5	G2	=TRANSPOSE(FILTER($A$2:$A$21,(ISNUMBER(SEARCH($E2&",",$C$2:$C$21,1))*ISNUMBER(SEARCH($F2&",",$C$2:$C$21,1)))=1))
Dynamic array formulas.

You will not need to do the TEXTSPLIT or have any issues if a customer bought more than one of any item.

Doug

 

[duggie33]

Sorry, I forgot to mention that if you want a count rather than the Transaction_IDs, in the formula, change TRANSPOSE to COUNT.

Doug

 

[DoctorofMadness]

What I had ended up doing was replacing all of the numbers with a string definition of what that item type was;

Item Desc	Item Type	# of Purchases containing	Total ratio
eggs	12	2341	13.07%
snack	17	2106	11.75%
cook	8	1806	10.08%
butter	3	1687	9.42%
cereal	5	1445	8.06%
ice cream	13	1407	7.85%
bbq	2	1271	7.09%
Chemicals	20	1258	7.02%
Pet Products	21	1163	6.49%
*******s	9	1096	6.12%
coffee	7	898	5.01%
bacon	1	527	2.94%
soft	19	381	2.13%
pizza	16	262	1.46%
nuts	14	158	0.88%

I then used a string search to perform the count on the "transaction IDs + Item Types" table, where we had the textsplit function.

=COUNTIF('Transaction IDs + Item Types'!C:C,F25&"*")

This formula went into the 3rd column of this pasted table, and as far as I know, should be giving an accurate result.


"Transaction IDs + Item Types" now looks like;

Transaction_ID	Customer_ID	Item_Types	Item Types
1​	15107144​	pet products,cereal,cook,snack,	pet products	cereal	cook	snack
2​	15107169​	cook,*******s,snack,	cook	*******s	snack
3​	15120097​	ice cream,cook,	ice cream	cook
4​	15128454​	cook,*******s,*******s,	cook	*******s
5​	15128488​	coffee,chemicals,chemicals,	coffee	chemicals
6​	15131912​	bbq,	bbq
7​	15134734​	bbq,butter,bbq,	bbq	butter
8​	15500173​	coffee,cook,bacon,bbq,chemicals,cereal,ice cream,bbq,	coffee	cook	bacon	bbq	chemicals	cereal	ice cream
9​	15502484​	cereal,	cereal
10​	15507087​	coffee,	coffee

And my confidence and support sheet now looks like this;

							Confidence
Antecedent1	Consequence1		# of Customers buying Antecedent	# of Customers buying Consequence	# of Customers buying both	Support	Association, Antecedent = Consequence	Association, Consequence = Antecedent
snack	eggs	-->	2106	2341	1172	0.26354846	0.556505223	0.500640752
snack	cook	-->	2106	1806	1010	0.258179959	0.479582146	0.559246955
snack	butter	-->	2106	1687	1002	0.264170841	0.475783476	0.593953764
snack	cereal	-->	2106	1445	1034	0.291185582	0.490978158	0.715570934
snack	ice cream	-->	2106	1407	661	0.188158269	0.313865147	0.469793888
snack	bbq	-->	2106	1271	893	0.26443589	0.424026591	0.702596381
snack	chemicals	-->	2106	1258	867	0.257728894	0.411680912	0.689189189
snack	pet products	-->	2106	1163	638	0.195166718	0.30294397	0.548581255
snack	*******s	-->	2106	1096	783	0.244534666	0.371794872	0.714416058
snack	coffee	-->	2106	898	410	0.136484687	0.194681861	0.456570156
snack	bacon	-->	2106	527	408	0.154956324	0.193732194	0.774193548
snack	soft	-->	2106	381	305	0.122637716	0.144824311	0.800524934
snack	pizza	-->	2106	262	251	0.105996622	0.119183286	0.958015267
snack	nuts	-->	2106	158	155	0.068462898	0.07359924	0.981012658

Let me know if you think there might be anything at fault with the countif() by string function I had wrote. Its my first time using it since a brief example in my freshman year excel class but I think it works okay?

Thanks,

 

[DoctorofMadness]

Oh I had also used your advice with the updated textsplit with unique(), hence why transaction ID 5 only says chemicals once, when it was bought twice

 

[Fluff]

Glad you sorted it & thanks for the feedback.