Finding the most frequent combination

[excelNewbie22]

hi!
how to find the most frequent combination of 1's and 0's (or just 1's), which bigger then 30,
could be 31 or 40, and etc....
it can skip columns, for instance, 30 and 39 and 40 to 42, or 1-9 and etc,
now, the data below is just a sample, the real data is way more big, like 2000 rows and 160 columns,
(which in this case i'll lookup to find combo which bigger then 120)
the possible combinations is soooooo biggggg, like hundres of millions,
i find it hard for excel to handle, but i thought maybe there's a clever way to approach it?

test
	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	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45
2	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
3	1	1	1	1	1	1	1	1	1	1	1	1	0	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
4	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
5	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	1	1	1	1	1	1	1	1	1
6	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
7	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
8	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
9	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
10	1	1	1	1	1	1	1	1	1	1	1	0	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
11	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1
12	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
13	1	1	1	1	1	1	1	1	1	1	0	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	1	1	1	1	1	1	1	1	1
14	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
15	1	1	1	1	1	1	1	1	1	1	0	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
16	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
17	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
test

 

[lrobbo314]

I asked the same question. I think that whatever they're trying to accomplish... There must be another way to better analyze the data. But, who knows?

Well... I left it running for several hours and it crashed. And that was with sample data of only 16 rows. So... yeah. Sorry. Don't think it's gonna happen.

 

[excelNewbie22]

lrobbo314 - completely understand, from the beginning i thought it was a long shot, but i had to ask,
and really appreciate you trying in python too!
thanks for all the time and effort!

 

[lrobbo314]

Well... I ran it again and it worked.

I used the first 41 columns from the sample data you provided.

This is the Python code.

import pandas as pd
from itertools import combinations
df = pd.read_csv("data.csv", header=None)
dict = {}
for index, row in df.iterrows():
    lst = list(row)
    combos = list(combinations(lst, 34))
    for combo_tup in combos:
        combo = "".join([str(e) for e in combo_tup])
        if not combo in dict:
            dict[combo] = 1
        else:
            dict[combo] += 1
output = zip(dict.keys(),dict.values())
odf = pd.DataFrame(output)
odf.to_csv('output.csv')

And these were the results.

output.csv
	A	B
1	Combination	Frequency
2	1111111111111111111111111111111111	219,418,251
3	1111111110111111111111111111111111	19,348,875
4	1111111111011111111111111111111111	14,697,540
5	1111111101111111111111111111111111	14,090,895
6	1111111111111111111111111111101111	9,680,680
7	1111111111111111111111111111011111	8,824,584
8	1111111111101111111111111111111111	6,094,036
9	1111111011111111111111111111111111	5,670,756
10	1111111111111111111111111111110111	3,727,440
11	1111111101111111111111111111101111	2,390,850
12	1111111111111101111111111110111111	2,165,800
13	1111111110111111111111111111101111	2,125,200
14	1111111101111111111111111111011111	1,912,680
15	1111111101111111111110111111111111	1,863,225
16	1111111111111011111111111110111111	1,749,300
17	1111111101111111111111011111111111	1,719,900
18	1111111110111111111111011111111111	1,656,200
19	1111111111111101111111111111011111	1,499,400
20	1111111111111111111111111110111111	1,409,632
21	1111111110111111111111111111011111	1,345,960
22	1111111111110111111111111111111111	1,320,176
23	1111111111111110111111111111011111	1,299,480
24	1111111111111110111111111110111111	1,299,480
25	1111111011111111111111111111011111	1,275,120
26	1111111110111111111110111111111111	1,242,150
27	1111110111111111111111111111111111	1,236,564
28	1111111011111111111111111111101111	1,214,400
29	1111111111111111111110111111111111	1,151,150
30	1111111011111111111110111111111111	1,146,600
31	1111111111111011111111111101111111	1,082,900
32	1111111110111111111111111111110111	1,062,600
33	1111111110111111111111101111111111	1,051,050
34	1111111111111101111111111111111111	1,047,200
35	1111111111111110111111111111111111	994,840
36	1111111111111101111111111101111111	928,200
37	1111111101111111111111111111110111	910,800
38	1111111111111111111111111101111111	906,192
39	1111111101111111111101111111111111	859,950
40	1111111111111111111111011111111111	837,200
41	1111111011111111111101111111111111	764,400
42	1111111111111011111111111111011111	749,700
43	1111111111111111111101111111111111	743,820
44	1111111101111111111111101111111111	675,675
45	1111111111011111111111111111101111	672,980
46	1111111011111111111111011111111111	655,200
47	1111111111110111111111111110111111	649,740
48	1111111111110111111111111101111111	649,740
49	1111111111111111111111111111111011	584,112
50	1111111111111011111111111111111111	549,780
51	1111111111111110111111111111101111	499,800
52	1111111111011111111111011111111111	496,860
53	1111111111111111011111111111011111	487,305
54	1111111111011111111111101111111111	455,455
55	1111111111111111011111111111111111	447,678
56	1111111111011111111111111111110111	425,040
57	1111110111111111111111111111011111	425,040
58	1111111110111111111101111111111111	420,420
59	1111111111111110111111111101111111	408,408
60	1111111111111111011111111110111111	357,357
61	1111111111111101111111111111101111	357,000
62	1111111111011111111111111111011111	346,104
63	1111111011111111111111111111110111	331,200
64	1111110111111111111101111111111111	308,700
65	1111111111111111111111101111111111	300,300
66	1111111110111111111111110111111111	300,300
67	1111110111111111111111111111101111	289,800
68	1111110111111111111110111111111111	286,650
69	1111111111011111111110111111111111	273,273
70	1111111111111111011111111111101111	270,725
71	1111111111011111111111110111111111	210,210
72	1111111110111111111111111111111011	202,400
73	1111111111101111111111111101111111	185,640
74	1111111111111111111011111111111111	177,100
75	1111111011111111111011111111111111	163,800
76	1111101111111111111111111111111111	138,426
77	1111111101111111111011111111111111	135,135
78	1111111111110111111111111111011111	129,948
79	1111111101111111111111111111111011	124,200
80	1111111011111111111111101111111111	120,120
81	1111111111011111111111111111111011	106,260
82	1111110111111111111011111111111111	95,550
83	1111111101111111111111110111111111	90,090
84	1111111111101111111111111110111111	86,632
85	1111111111111111011111111101111111	85,085
86	1111111111111011111111111111101111	83,300
87	1111110111111111111111011111111111	76,440
88	1111111111111111101111111111111111	74,613
89	1111111111111110111111111111110111	71,400
90	1111111111011111111101111111111111	70,070
91	1111101111111111111111111111011111	69,552
92	1111111111111111101111111111011111	63,063
93	1111111111111111011111111111110111	62,475
94	1111101111111111111101111111111111	52,920
95	1111110111111111111111111111110111	50,400
96	1111111111111111111111110111111111	50,050
97	1111111110111111111011111111111111	50,050
98	1111111111111111101111111111101111	47,775
99	1111111111011111111111111011111111	45,045
100	1111111111111111111111111111111101	35,537
101	1111111111111111101111111110111111	35,035
102	1111101111111111111111111111101111	30,240
103	1111111110111111111111111011111111	30,030
104	1111111011111111111111111111111011	28,800
105	1111101111111111111011111111111111	26,460
106	1111111111111101111111111111110111	23,800
107	1111101111111111111110111111111111	22,932
108	1111111111011111111111111101111111	22,880
109	1111111111111111101111111111110111	15,925
110	1111111110111111111111111111111101	13,800
111	1111111111011111111111111111111101	10,120
112	1111011111111111111111111111111111	7,050
113	1111111111011111111011111111111111	6,435
114	1111111111111111101111111101111111	6,435
115	1111111101111111111111111111111101	5,400
116	1111111111111111011111111111111011	5,355
117	1111011111111111111111111111011111	5,040
118	1111011111111111111011111111111111	3,150
119	1111111111111111111111111011111111	3,003
120	1111011111111111111101111111111111	2,940
121	1111111111111110111111111111111011	2,856
122	1111101111111111111111111111110111	2,520
123	1111111111111111101111111111111011	2,205
124	1111110111111111111111111111111011	2,100
125	1111011111111111111111111111101111	1,050
126	1111111111111111111111111111111110	629
127	1111111011111111111111111111111101	600
128	1111111111011111111111111111111110	276
129	1111111110111111111111111111111110	240
130	1110111111111111111011111111111111	120
131	1110111111111111111111111111011111	120
132	1110111111111111111111111111111111	120
133	1111111111111111011111111111111101	119
134	1111111111111111101111111111111101	105
135	1111111101111111111111111111111110	45
136	1111111111111111101111111111111110	1
output

 

[excelNewbie22]

wow! truly!

yet, while still really appreciate all your effort,
the result aren't what i meant,

taking the second result for example: 1111111110111111111111111111111111
i need the know which corresponding columns making this combination, it could be
a2:d2 & f2:ah2 = 34 or a2 & d2 & f2 & g2:aj2 = 34 or any of the 22,000,000 combinations,

and then, only keep this combination (along with his corresponding columns) if the combination repeats more then 10% from entire range (in above sample, only 16 rows),
if need to i can provide larger sample for testing

for me it'll take some time to understand how to test and use it in python since i know nothing

again, many thanks for keep trying to help!