Count distinct value pairs in multiple columns

[Kishan]

Using Excel 2000

Hi,
Pairs S5 ToRight
Multiple columns combinations R6 ToDown

Data Columns C:P (P1 To P14)
Cell R6 = 1|2|3|4|5|6|7 mean (P1+P2+P3+P4+P5+P6+P7) count Pairs S5 (1|1|1|1|1|1|1) result S6 = 34
Cell R7 = 1|2|3|4|5|6|8 mean (P1+P2+P3+P4+P5+P6+P8) count Pairs S5 (1|1|1|1|1|1|1) result S7 = 28

Example: I have count using AutoFilter as there are 14 columns so COMBIN(14,7) = 3432 I believe need VBA

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Jor	Temp	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	EM 1
2	Jor	Temp	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	EM 1
3	Jor	Temp	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	EM 1
4	Jor	Temp	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	EM 1	Multiple	Counts	Counts	Counts
5	Jor	Temp	1	2	3	4	5	6	7	8	9	10	11	12	13	14	EM 1	Columns	1|1|1|1|1|1|1	X|X|2|2|1|2|1	1|1|1|1|1|1|X
6	1	70/71	1	1	1	1	1	1	1	1	1	1	2	X	X	1		1|2|3|4|5|6|7	34	1	10
7	2	70/71	1	1	1	1	1	1	1	1	1	1	2	X	X	1		1|2|3|4|5|6|8	28		7
8	3	70/71	1	1	1	1	1	1	2	1	2	1	1	1	X	1		1|2|3|4|5|6|9	25		15
9	4	70/71	1	1	1	1	1	1	1	1	1	2	X	1	X	X		1|2|3|4|5|6|10	28		9
10	5	70/71	1	1	1	1	1	1	1	1	1	1	X	2	1	1		1|2|3|4|5|6|11	19		22
11	6	70/71	1	1	1	1	1	1	2	1	1	X	1	2	1	1		1|2|3|4|5|6|12	22		14
12	7	70/71	1	1	1	1	1	1	1	X	X	2	1	2	X	1		1|2|3|4|5|6|13	12		15
13	8	70/71	1	1	1	1	1	1	X	1	1	1	2	1	1	2		1|2|3|4|5|6|14	26		13
14	9	70/71	1	1	1	1	1	1	X	1	X	X	2	2	2	X		1|2|3|4|5|7|8
15	10	70/71	1	1	1	1	1	1	X	1	2	1	1	1	2	1		1|2|3|4|5|7|9
16	11	70/71	1	1	1	1	1	1	1	X	1	1	1	X	1	2		1|2|3|4|5|7|10
17	12	70/71	1	1	1	1	1	1	1	2	X	1	2	1	2	2		1|2|3|4|5|7|11
18	13	70/71	1	1	1	1	1	1	1	X	X	X	2	1	X	2		1|2|3|4|5|7|12
19	14	70/71	1	1	1	1	1	1	2	1	1	1	X	2	1	1		1|2|3|4|5|7|13
20	15	70/71	1	1	1	1	1	1	X	1	1	1	X	1	2	2		1|2|3|4|5|7|14
21	16	70/71	1	1	1	1	1	1	1	2	X	2	1	X	1	X		1|2|3|4|5|8|9
22	17	70/71	1	1	1	1	1	1	1	1	1	1	X	X	X	X		1|2|3|4|5|8|10
23	18	70/71	1	1	1	1	1	1	X	1	1	X	X	X	1	1		1|2|3|4|5|8|11
24	19	70/71	1	1	1	1	1	1	1	1	1	1	X	X	X	X		1|2|3|4|5|8|12
25	20	70/71	1	1	1	1	1	1	1	1	X	1	1	1	2	1		1|2|3|4|5|8|13
26	21	70/71	1	1	1	1	1	1	1	X	1	X	X	X	2	1		1|2|3|4|5|8|14
27	22	70/71	1	1	1	1	1	1	1	1	X	1	1	1	2	1		1|2|3|4|5|9|10
28	23	70/71	1	1	1	1	1	1	1	1	2	1	1	1	2	1		1|2|3|4|5|9|11
29	24	70/71	1	1	1	1	1	1	1	1	1	2	X	1	1	X		1|2|3|4|5|9|12
30	25	70/71	1	1	1	1	1	1	1	1	X	2	1	2	2	X		1|2|3|4|5|9|13
31	26	70/71	1	1	1	1	1	1	1	1	X	1	X	X	2	2		1|2|3|4|5|9|14
32	27	70/71	1	1	1	1	1	1	X	1	1	X	1	2	2	1		1|2|3|4|5|10|11
33	28	70/71	1	1	1	1	1	1	1	1	X	2	X	2	2	1		1|2|3|4|5|10|12
34	29	70/71	1	1	1	1	1	1	1	1	X	2	1	2	2	X		1|2|3|4|5|10|13
35	30	70/71	1	1	1	1	1	1	1	1	X	1	X	X	2	2		1|2|3|4|5|10|14
36	31	70/71	1	1	1	1	1	1	X	1	1	X	X	X	1	1		1|2|3|4|5|11|12
37	32	70/71	1	1	1	1	1	1	1	1	1	1	X	X	X	X		1|2|3|4|5|11|13
38	33	70/71	1	1	1	1	1	1	1	1	X	1	1	1	2	1		1|2|3|4|5|11|14
39	34	70/71	1	1	1	1	1	1	1	2	X	1	1	X	2	X		1|2|3|4|5|12|13
40	35	70/71	1	1	1	1	1	1	1	1	2	1	1	1	X	1		1|2|3|4|5|12|14
41	36	70/71	1	1	1	1	1	1	2	1	1	X	2	2	2	1		1|2|3|4|5|13|14
42	37	70/71	1	1	1	1	1	1	1	1	2	1	1	1	2	1		1|2|3|4|6|7|8
43	38	70/71	1	1	1	1	1	1	1	1	1	2	X	1	1	X		1|2|3|4|6|7|9
44	39	70/71	1	1	1	1	1	1	1	X	1	1	1	X	1	2		1|2|3|4|6|7|10
45	40	70/71	1	1	1	1	1	1	1	X	1	X	1	1	2	2		1|2|3|4|6|7|11
46	1	71/72	1	1	1	1	1	1	1	1	1	2	X	1	X	X		1|2|3|4|6|7|12
47	2	71/72	1	1	1	1	1	1	1	1	1	1	X	2	1	1		1|2|3|4|6|7|13
48	3	71/72	1	1	1	1	1	1	1	1	2	1	1	1	X	1		1|2|3|4|6|7|14
49	4	71/72	1	1	1	1	1	1	1	X	2	1	X	1	X	1		1|2|3|4|6|8|9
50	5	71/72	1	1	1	1	1	1	X	1	1	2	X	1	X	X		1|2|3|4|6|8|10
51	6	71/72	1	1	1	1	1	1	X	1	2	1	X	1	X	1		1|2|3|4|6|8|11
52	7	71/72	1	1	1	1	1	1	1	1	X	2	X	2	2	1		1|2|3|4|6|8|12
53	8	71/72	1	1	1	1	1	1	X	1	1	1	X	1	2	1		1|2|3|4|6|8|13
54	9	71/72	2	1	X	2	1	X	X	X	1	X	X	1	2	X		1|2|3|4|6|8|14
55	10	71/72	X	X	2	1	X	1	1	2	1	1	2	1	2	1		1|2|3|4|6|9|10
56	11	71/72	1	X	2	X	1	2	1	2	X	X	1	1	2	X		1|2|3|4|6|9|11
57	12	71/72	X	2	X	1	1	1	1	1	1	2	1	X	X	X		1|2|3|4|6|9|12
58	13	71/72	1	1	X	1	1	X	1	1	X	X	1	1	1	1		1|2|3|4|6|9|13
59	14	71/72	1	1	X	1	1	1	X	1	1	1	1	1	1	1		1|2|3|4|6|9|14
60	15	71/72	2	1	X	1	1	1	2	1	1	X	X	X	1	X		1|2|3|4|6|10|11
61	16	71/72	X	1	2	2	X	1	1	X	1	1	1	1	1	1		1|2|3|4|6|10|12
62	17	71/72	2	1	1	1	2	2	1	1	2	2	X	X	1	2		1|2|3|4|6|10|13
63	18	71/72	1	1	1	2	1	1	1	1	1	X	2	1	1	1		1|2|3|4|6|10|14
64	19	71/72	1	X	2	1	X	1	1	2	1	X	2	2	1	X		1|2|3|4|6|11|12
65	20	71/72	1	X	1	X	1	1	X	X	1	1	1	X	1	1		1|2|3|4|6|11|13
66	21	71/72	X	X	X	1	1	1	1	1	1	1	1	1	1	1		1|2|3|4|6|11|14
67	22	71/72	X	X	2	2	1	2	1	X	1	1	X	1	2	X		1|2|3|4|6|12|13
68	23	71/72	1	1	2	1	1	1	1	1	X	1	1	1	1	1		1|2|3|4|6|12|14
69	24	71/72	X	1	1	1	X	1	1	2	1	1	2	1	X	X		1|2|3|4|6|13|14
70	25	71/72	1	1	1	2	X	1	X	1	1	X	1	X	X	1		1|2|3|4|7|8|9
71	26	71/72	1	X	1	1	1	2	X	1	X	X	X	2	1	X		1|2|3|4|7|8|10
72	27	71/72	1	1	X	1	X	X	X	1	1	1	X	1	2	X		1|2|3|4|7|8|11
73	28	71/72	1	X	1	X	1	X	2	2	X	1	1	1	1	2		1|2|3|4|7|8|12
74	29	71/72	1	1	X	2	2	1	1	1	X	1	1	1	1	2		1|2|3|4|7|8|13
75	30	71/72	1	X	X	1	X	1	1	1	2	1	1	1	1	X		1|2|3|4|7|8|14
76	31	71/72	1	X	X	X	X	1	2	1	1	1	1	1	2	1		1|2|3|4|7|9|10
77	32	71/72	2	X	1	X	1	1	1	1	1	2	X	1	2	2		1|2|3|4|7|9|11
78	33	71/72	1	1	X	1	2	2	2	1	1	X	1	X	X	2		1|2|3|4|7|9|12
79	34	71/72	1	1	1	1	X	1	1	1	1	1	1	X	X	1		1|2|3|4|7|9|13
80	35	71/72	1	1	1	1	2	1	X	1	1	X	1	1	1	1		1|2|3|4|7|9|14
81	36	71/72	2	X	2	X	1	2	1	X	1	X	1	X	X	1		1|2|3|4|7|10|11
82	37	71/72	2	1	X	1	1	1	1	1	2	X	1	1	1	X		1|2|3|4|7|10|12
83	38	71/72	1	1	1	1	X	1	X	1	2	1	1	1	X	2		1|2|3|4|7|10|13
84	1	72/73	X	1	2	1	2	X	X	1	1	2	X	1	1	2		1|2|3|4|7|10|14
85	2	72/73	1	X	2	2	2	1	X	X	2	2	X	X	1	1		1|2|3|4|7|11|12
86	3	72/73	1	X	1	1	X	X	2	2	X	X	1	1	X	X		1|2|3|4|7|11|13
87	4	72/73	1	X	1	2	2	1	X	1	1	X	1	2	2	1		1|2|3|4|7|11|14
88	5	72/73	X	1	X	1	X	1	1	X	1	1	1	X	X	1		1|2|3|4|7|12|13
89	6	72/73	X	X	X	1	1	1	1	X	1	X	2	1	2	X		1|2|3|4|7|12|14
90	7	72/73	2	X	1	1	1	2	X	X	X	1	1	1	2	1		1|2|3|4|7|13|14
Sheet1

Thank you in advance

Regards,
Kishan

 

[Kishan]

Hi Ombir, thank you for your kind help I appreciate your help very much, I am sorry for the trouble and hassle that I have caused you.

I tried both code are in Post#18 & Post#20, they stop at the line below.

Post#18

nar(i) = Join(Application.Index(arr, i, 0), "|")

Post#20

 nar(i) = Join(Application.Index(arr, i, 0), "|")

Please can you check?

Kind Regards,
Kishan

 

[Kishan]

See if this modified version of the code I posted earlier works for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub PatternCounts()
  Dim R As Long, C As Long, X As Long, Z As Long, Cnt As Long
  Dim Pattern As String, Lines(1 To 14) As String
  Dim Data As Variant, Results As Variant
  Data = Range("C6", Cells(Rows.Count, "P").End(xlUp))
  Results = Range("R5", Cells(Cells(Rows.Count, "R").End(xlUp).Row, Cells(5, Columns.Count).End(xlToLeft).Column))
  For R = 2 To UBound(Results, 1)
    For X = 1 To UBound(Data)
      For Z = 1 To 14
        Lines(Z) = Data(X, Z)
      Next
      Pattern = Join(Application.Index(Lines, 1, Split(Results(R, 1), "|")), "|")
      For C = 2 To UBound(Results, 2)
        If Pattern = Results(1, C) Then Results(R, C) = Results(R, C) + 1
      Next
    Next
  Next
  Range("R5").Resize(UBound(Results, 1), UBound(Results, 2)) = Results
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you Rick, Your help means a lot to me. I am grateful to you for making a modification to work with my version. I am sorry for the trouble and hassle that I have caused you.

Above modified code, which is working with my full database like magic. Problem Solved!!

Good Luck

Have a nice weekend

Kind Regards,
Kishan