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]

How about this?

Excel
	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	AT	AU	AV
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		Combination	Frequency
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		1111111111111111111111111111111111	118
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		1111111111110111111111111111111111	11
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		1111111111110111111111111111111110	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		1111111111111111111111111111111110	2
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		1111111111011111111111111101111111	12
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		1111111111101111111111111111111111	12
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		1111111111111111101111111111111110	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		1111111111111111101111111111111111	11
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		1111111111011111111111111111111111	23
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		1111111111011111111111111111111110	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
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:AG17	Cell Value	=0	text	NO
AU2:AV11	Expression	=MOD(ROW(),2)=0	text	YES

Sub WCMBO()
Dim LR As Long:         LR = Range("A" & Rows.Count).End(xlUp).Row
Dim base() As Variant:  base = Range("A2:AG" & LR).Value
Dim inc() As Variant:   inc = Range("AH2:AS" & LR).Value
Dim SD As Object:       Set SD = CreateObject("Scripting.Dictionary")
Dim tmp As String, tv As String

For i = 1 To UBound(base)
    tmp = Join(Application.Index(base, i, 0), "")
    For j = 1 To UBound(inc, 2)
        tv = tmp & inc(i, j)
        SD(tv) = SD(tv) + 1
    Next j
Next i

Range("AU2").Resize(SD.Count).Value = Application.Transpose(SD.keys())
Range("AV2").Resize(SD.Count).Value = Application.Transpose(SD.items())
End Sub

 

[excelNewbie22]

lrobbo314 -thank you for keep trying to help,
but i don't understand how did you get these combinations from this data set?
for example first combination in au2 is: 1111111111111110000000000000000000,
none of the rows have that much zero's,
and also the frequency, 118 times of the above combination doesn't sound right

 

[lrobbo314]

I don't know what you're talking about when you say "for example first combination in au2 is: 1111111111111110000000000000000000"

The value I posted in AU2 is all 1s.

The code goes row by row. Starting in row 2, it combines the first 33 values, e.g. A2:AG2, into a string. Then it goes one by one. Essentially, the first value will be A2:AG2 + AH2 combined into a string. Then A2:AG2 + AI2, A2:AG2 + AJ2, A2:AG2 + AK2, and so on.

As it is making all of these strings, it is adding them to a dictionary and tallying them.

The combinations from the results I posted have at most 2 zeroes in any individual combination.

EXCEL
	AU	AV
1	Combination	Frequency
2	1111111111111111111111111111111111	118
3	1111111111011111111111111111111111	23
4	1111111111011111111111111101111111	12
5	1111111111101111111111111111111111	12
6	1111111111110111111111111111111111	11
7	1111111111111111101111111111111111	11
8	1111111111111111111111111111111110	2
9	1111111111011111111111111111111110	1
10	1111111111110111111111111111111110	1
11	1111111111111111101111111111111110	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AU2:AV11	Expression	=MOD(ROW(),2)=0	text	YES

 

[excelNewbie22]

i see, apparently i did something wrong, when i copied all to excel, it's showed me: 1.11111E+33, so i format it as a number and it gave me 1111111111111110000000000000000000, so apologies, and thank you (how to see it as the full number?)

i still didn't understand how you got to 118, when you say "Then it goes one by one." do you mean A2:AG2 and then A3:AG3 and A4:AG4 all the way down? if not, this is what i meant and after, and from this list of results (A2:AG2,A3:AG3,A4:AG4,A5:AG5,A6:AG6.........) i want to know if there's some combination which exceeds 10%

 

[lrobbo314]

I am trying to go off of the formulas that you posted in Post #8.

Let's pretend that what I am posting below is just one row worth of data. Each row, will have 12 different combinations. The first 33 characters are always the same. Then, it starts at column 34 and appends it to the end. Then, it keeps that initial base of the first 33 characters, but adds the value from column 35, and so on.

After it gets to column 45, it would move on the the next row, get a new base of the first 33 characters, and repeat the process.

Like I said, what is posted below is an illustration of the steps for just 1 row. This was my understanding of how the combinations are being made.

Book1
	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	AT	AU
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		Combinations
2	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
3	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
4	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111110
5	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
6	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
7	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
8	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
9	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
10	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
11	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
12	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
13	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	1	1	1	0	1	1	1	1	1	1	1	1	1		1111111111111011111111111111111111
Sheet1

Cell Formulas
Range	Formula
AU2:AU13	AU2	=TEXTJOIN("",,A2:AG2,INDEX($AH$2:$AS$13,ROW()-1,ROW()-1))

 

[excelNewbie22]

i understand what you say, but "Each row, will have 12 different combinations" isn't what i meant by the example in post #8,
the number of combinations is so much bigger, millions bigger, because the combinations could also be a2:d2 & f2:ah2 = 34,
not just the first 33 & each one of the remaining,
or a2 & d2 & f2 & g2:aj2 =34 and so on

btw, for calculating and getting all possible combinations i often use this site Combinations and Permutations Calculator and for 34/41 the combinations are 22,481,940 !

and like you say, this is just for one row

 

[lrobbo314]

Ok. I'm starting to better understand what you're going after.

But, I'm still a little confused. You said 34/41 for 22,481,940 combinations. But there are 45 columns. Shouldn't the combination calculation be 34/45 which would be 1,0150,595,910 combinations per row?

Even if you're right and it's 22 million... 22 million x 2,000 rows is 44,963,880,000 different combinations! Or, if my calculation of 34/45 is correct... about 20 trillion combinations. What could possibly be the reason to compare that many combinations?

 

[excelNewbie22]

the 45 columns was just an example, the real data is 41,
(actually have two sets of data, one 41 and one 150 like i mention in post#1)
in my data each column represent a data and i'm trying to find similarity for conclusions,
but no matter how big the combinations are,
if possible by excel i would glad to try it out

 

[lrobbo314]

Unfortunately, I think this is an unrealistic request. Excel CANNOT handle it. The VBA code was exceedingly slow.

So, I wrote some code in Python. It looked promising. But, I ran 16 rows, with the 34/41 ratio. And that was like 2.5 hours ago, and it's still going. And it's taxing the S#!& out of my computer.

I'll let you know when it finishes. But this doesn't seem realistic. People say Python is slow. So maybe doing it in Java or some variant of C would be feasible.

But, I don't really know Java or C.

Think I've hit the limit of being able to help on this one.

 

[johnnyL]

Now, maybe, you can see the reason for my post #9.

I don't see what the goal is, what purpose could it possibly serve? To test what coders/Excel can do? I don't know.

Maybe we should try to find out what the last digit of 22/7 is with no remainder?