Pairwise Comparison Table

[Samgraphics]

Hi, I'm trying to create a pairwise/tripple-wise comparison table in Excel. I checked ChatGPT but the solution it gave me isn't working. Can anyone help me? I'm comparing the correlation analysis between numbers to determine if any pairs or sets of numbers tend to appear together more often and then highlight pairs with high correlation coefficients.

ChatGPT suggested
In Sheet1 enter my lotter data.
in a new sheet2, create a grid where rows and columns represent the numbers (1 to 35). start by listing numbers 1 to 35 down column A and across row 1.I will have numbers 1 to 35 in cells A2 to A36 and in cells B1 to AJ1.

Then in Sheet2 B2 enter this formula =SUMPRODUCT((Sheet1!$B$2:$F$100=$A2)*(Sheet1!$B$2:$F$100=B$1))

But this isn't working.

This is the data

LOTTERY ANALYSIS.xlsx
	A	B	C	D	E	F	G	H
1	Draw #	P1	P2	P3	P4	P5	B1	B2
2	1	3	11	19	29	33	3	12
3	2	8	9	14	25	29	4	6
4	3	3	17	22	26	28	7	12
5	4	7	13	16	25	32	9	11
6	5	10	12	13	24	25	11	12
7	6	13	21	25	28	32	2	10
8	7	7	10	13	28	31	4	5
9	8	2	6	7	8	15	7	10
10	9	2	4	17	20	27	1	8
11	10	2	16	21	23	27	1	8
12	11	5	8	13	26	35	5	9
13	12	4	5	18	22	30	5	12
14	13	2	24	26	30	34	6	7
15	14	4	10	18	27	32	10	12
16	15	6	8	16	20	24	7	8
17	16	1	13	22	26	31	2	10
18	17	7	14	23	25	29	2	11
19	18	7	9	10	15	24	4	12
20	19	4	6	10	11	25	7	9
21	20	5	6	18	28	30	2	7
Sheet2

This is the comparison table (I only copied part of it to reduce size.)

LOTTERY ANALYSIS.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		1	2	3	4	5	6	7	8	9	10
2	1	1	0	0	0	0	0	0
3	2	0	4	0	0	0	0	0
4	3	0	0	2	0	0	0	0
5	4	0	0	0	4	0	0	0
6	5	0	0	0	0	3	0	0
7	6	0	0	0	0	0	4	0
8	7	0	0	0	0	0	0	5
9	8	0	0	0	0	0	0	0
10	9	0	0	0	0	0	0	0
11	10	0	0	0	0	0	0	0
12	11	0	0	0	0	0	0	0
13	12	0	0	0	0	0	0	0
14	13	0	0	0	0	0	0	0
15	14	0	0	0	0	0	0	0
16	15	0	0	0	0	0	0	0
17	16	0	0	0	0	0	0	0
18	17	0	0	0	0	0	0	0
19	18	0	0	0	0	0	0	0
20	19	0	0	0	0	0	0	0
21	20	0	0	0	0	0	0	0
22	21	0	0	0	0	0	0	0
23	22	0	0	0	0	0	0	0
24	23	0	0	0	0	0	0	0
25	24
26	25
27	26
28	27
29	28
30	29
Sheet3

Cell Formulas
Range	Formula
B2:H24	B2	=SUMPRODUCT((Sheet2!$B$2:$F$100=B$1)*(Sheet2!$B$2:$F$100=$A2))

pleaes help.
Thank you

 

[Kaper]

I don't think it will be easy with formulas. especially having in mind you have a bit older version of Excel.

Such a macro will do the job in almost no-time.

Sub pairs_frq_table()
Dim i As Long, j As Long, k As Long, lr As Long
Dim src As Variant, outp(1 To 35, 1 To 35) As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
src = Range(Cells(2, 2), Cells(lr, 8)).Value
For i = 1 To lr - 1
  For j = 1 To 6
    For k = j + 1 To 7
      outp(src(i, j), src(i, k)) = outp(src(i, j), src(i, k)) + 1
      outp(src(i, k), src(i, j)) = outp(src(i, j), src(i, k))
    Next k
  Next j
Next i
Range("K2").Resize(35, 35).Value = outp
End Sub

You may add 1:35 to verical and horizontal headers of output table manually. and when you add new rows of lottery results, just rerun the macro. It will replace the results.

From the point of view of calculations - triplets will be not much more work, but how would you like to present them? the result will be no sqare (2D) table, but a cube (3D). Of course it is possible to list all triplets in a list (1-D table)
1-2-3 ; count of 1-2-3
1-2-4 ; count of 1-2-4
and so on, but then we shall probably not allow for listing all 6 identical results for
1-2-3
1-3-2
2-1-3
2-3-1
3-1-2
3-2-1
and that makes programming somewhat harder

 

[Samgraphics]

I don't think it will be easy with formulas. especially having in mind you have a bit older version of Excel.

Such a macro will do the job in almost no-time.

Sub pairs_frq_table()
Dim i As Long, j As Long, k As Long, lr As Long
Dim src As Variant, outp(1 To 35, 1 To 35) As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
src = Range(Cells(2, 2), Cells(lr, 8)).Value
For i = 1 To lr - 1
  For j = 1 To 6
    For k = j + 1 To 7
      outp(src(i, j), src(i, k)) = outp(src(i, j), src(i, k)) + 1
      outp(src(i, k), src(i, j)) = outp(src(i, j), src(i, k))
    Next k
  Next j
Next i
Range("K2").Resize(35, 35).Value = outp
End Sub

You may add 1:35 to verical and horizontal headers of output table manually. and when you add new rows of lottery results, just rerun the macro. It will replace the results.

From the point of view of calculations - triplets will be not much more work, but how would you like to present them? the result will be no sqare (2D) table, but a cube (3D). Of course it is possible to list all triplets in a list (1-D table)
1-2-3 ; count of 1-2-3
1-2-4 ; count of 1-2-4
and so on, but then we shall probably not allow for listing all 6 identical results for
1-2-3
1-3-2
2-1-3
2-3-1
3-1-2
3-2-1
and that makes programming somewhat harder

Hi thank you for you timely reply and help. thanks so much for you time and expertise most of all. I'm not very well versed in Excel or coding, so this was very confusing for me to say the list. However I was able to get it to work. It wasn't elegant at all. lol. Anyway. the later part of your reply I have absolutely no clue what you mean. I think I get the part where you're asking if I want it to list the triplets in just one order or all possible order? I think but the 1-D, 2-D, 3-D I don't understand so I'll ask you to use your best judgement since you're the expert here I will follow whatever directions you suggest. One other question is that the lottery I play has two drums, 1-35 main drum 1-12 bonus drum. 5 numbers are drawn from the main drum and 2 from bonus drum making a total of seven. I don't know if that would make the code too complicated. I had to adjust the code you provided to make it work with only 5 numbers so I just changed the 6 to 5 and it seemed to work. I went over to check the results to see if it was giving accurate readings and it was so I left it. Here is the code I adjusted.

Sub pairs_frq_table()
Dim i As Long, j As Long, k As Long, lr As Long
Dim src As Variant, outp(1 To 35, 1 To 35) As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
src = Range(Cells(2, 2), Cells(lr, 6)).Value
For i = 1 To lr - 1
  For j = 1 To 5
    For k = j + 1 To 5
      outp(src(i, j), src(i, k)) = outp(src(i, j), src(i, k)) + 1
      outp(src(i, k), src(i, j)) = outp(src(i, j), src(i, k))
    Next k
  Next j
Next i
Range("K2").Resize(35, 35).Value = outp
End Sub

Thank you very much

 

[Kaper]

Just a brief answer:
1-D, 2-D, 3-D means one dimentional, two dimentional and three dimentional respectively.

In 1-D data is presented that way that each pair (or triplet) is listed in one column and in next column the count of such pair/triplet is presented. (it can be seen as a line with headers on its side).
in 2-D you have (like Excel cell adressing) i headers with both elements of pair (one in horizontal header second in vertical) and on crossing of column and row there is a count of such pair. In 3-D the third element of tripled shall be presented in a direction perpendicular to 1st and second, so horizontal, but not left-right, but facing towards us-backward.

As for the changes in a code:
Now it focuses only on the first drum. I shall say that I've not noticed that the numbers could repeat (and they do - it's visible already in first row of your sample data).
So shall these numbers from second drum be also included? They could be and they were in original code, threated the same way as these from main drum. But I could imagine thet headers of the table could read: 1,2,...,34,35,1,2,...,11,12 so whole table would be 47 by 47

 

[Samgraphics]

Just a brief answer:
1-D, 2-D, 3-D means one dimentional, two dimentional and three dimentional respectively.

In 1-D data is presented that way that each pair (or triplet) is listed in one column and in next column the count of such pair/triplet is presented. (it can be seen as a line with headers on its side).
in 2-D you have (like Excel cell adressing) i headers with both elements of pair (one in horizontal header second in vertical) and on crossing of column and row there is a count of such pair. In 3-D the third element of tripled shall be presented in a direction perpendicular to 1st and second, so horizontal, but not left-right, but facing towards us-backward.

As for the changes in a code:
Now it focuses only on the first drum. I shall say that I've not noticed that the numbers could repeat (and they do - it's visible already in first row of your sample data).
So shall these numbers from second drum be also included? They could be and they were in original code, threated the same way as these from main drum. But I could imagine thet headers of the table could read: 1,2,...,34,35,1,2,...,11,12 so whole table would be 47 by 47

Thank you very much for your help and your time. Thank you