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

 

[offthelip]

Just an idea I have no time to test it:
Multiply each row by the row number up to 126 then start again from 1
Change this number to it's ascii character, concatenate a, b c etc for multiples of 127.
Concatenate all the rows together at the bottom of each column you can the count the character strings

 

[jkpieterse]

I would insert a column and use the CONCAT function to join together all cells of each row. Then create a pivot table of just the new column. Drag the field to both the row and the value area and sort on the value field (Descending).

 

[excelNewbie22]

offthelip - thank you but i didn't understand, maybe someone can demonstrate ?
jkpieterse - if i understood correctly you talking about all the values joined? if so, it isn't my intention, if not, didn't understand you too
sample will help me to understand

 

[I_know_nuffin]

'/*******************************************************************\
'
' copy the below Sub and function into a module in your workbook
'
' In the below change the line of code
' If Cells(x, countones) >= 20 Then
' to suit the count of ones your testing for, I used 20
'
' run the below code to fill in the ConcatRow for all rows
'
' create a pivot table based on these 2 columns
' Rows = ConcatRow , Values = Count of countofones
' sort the pivot table on 'Count of countofones' Largest to Smallest
'
'\*******************************************************************/

Sub concatrow()
Dim rowno As Long, colno As Long
Dim countones As Long
Dim x As Long, y As Long
Dim newstr As String

Range("A1").Activate

rowno = LastRowColumn(ActiveSheet, "R")
colno = LastRowColumn(ActiveSheet, "C")

Cells(1, colno + 1) = "CountofOnes"
Cells(1, colno + 2) = "ConcatRow"

countones = colno + 1


For x = 2 To rowno ' exclude header row
newstr = "" ' reset string

' this counts the number of ones in the row
Cells(x, countones).FormulaR1C1 = "=COUNTIF(RC[-" & colno & "]:RC[-1],1)"

If Cells(x, countones) >= 20 Then


For y = 1 To colno ' for all columns
newstr = newstr & Cells(x, y)
Next y
Cells(x, y + 1) = newstr
End If
Next x

End Sub





Function LastRowColumn(sht As Worksheet, RowColumn As String) As Long
'PURPOSE: Function To Return the Last Row Or Column Number In the Active Spreadsheet
'INPUT: "R" or "C" to determine which direction to search

Select Case LCase(Left(RowColumn, 1)) 'If they put in 'row' or column instead of 'r' or 'c'.
Case "c"
LastRowColumn = sht.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Case "r"
LastRowColumn = sht.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Case Else
LastRowColumn = 1
End Select

End Function

 

[excelNewbie22]

I_know_nuffin - so sorry for the late response, i didn't login for a long time, and even when i did, a few days ago, i didn't saw that,
anyway, did as you instructed and it doesn't make any sense, cause the concat rows returned wrong 1's and 0's (in a different db), for example instead of 41 cells = 11111101111111111111111111111111111110011 it returned 11111101111111100000000000000000000000000

 

[lrobbo314]

Not sure If this is what you're after exactly. But 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	AW
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	Count
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	45		9	2
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	12,31		10	3
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	45		11	2
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	35,9		12	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	45		15	2
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	44		17	1
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	10,15,18		18	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	45		24	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,33		31	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	17,15,11		33	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	45		34	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	10,24,9		35	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	45		44	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	10,34		45	8
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	45
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	45
Data

Cell Formulas
Range	Formula
AV1:AW15	AV1	=LET(ts,TEXTSPLIT(TEXTJOIN(",",,AT2:AT17),,",")+0,u,SORT(UNIQUE(ts)),VSTACK({"Combination","Count"},HSTACK(u,BYROW(u,LAMBDA(br,SUM(--(br=ts)))))))
AT2:AT17	AT2	=TEXTJOIN(",",,LET(f,FREQUENCY(IF(A2:AS2=1,COLUMN(A2:AS2)),IF(A2:AS2<>1,COLUMN(A2:AS2))),FILTER(f,f>0)))
Dynamic array formulas.

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

 

[excelNewbie22]

lrobbo314 - appreciate the try, but it isn't that

here's more detail example,
in col at there's the first combination of 34 columns and in au the second....
so i would like to find if there is 10% and above of duplicates of any combinations from col at,
then from the second combination in col au,
and so on all the way up to millions

and ofcourse if there is, save it with the column numbers aka first row headers like:
1-0-0-1-0-1-0-1-0-0-0-0-1-1-1-1-1-1-1-1-1-1-0-0-1-1-0-1-0-1-1-1-1-1
1-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

but excel can't contain it,
so i thought if there is a smart way to do so

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	AT	AU	AV	AW	AX	AY
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	first combination of 34 columns	second combination of 34 columns	third combination of 34 columns
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	1111111111111111111111111111111111	1111111111111111111111111111111111
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	1111111111110111111111111111111111	1111111111110111111111111111111111
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	1111111111111111111111111111111111	1111111111111111111111111111111111	1111111111111111111111111111111111
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	1111111111111111111111111111111111	1111111111111111111111111111111111	1111111111111111111111111111111110
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	1111111111111111111111111111111111	1111111111111111111111111111111111	1111111111111111111111111111111111
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	1111111111111111111111111111111111	1111111111111111111111111111111111	1111111111111111111111111111111111
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	1111111111011111111111111101111111	1111111111011111111111111101111111	1111111111011111111111111101111111
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	1111111111111111111111111111111111	1111111111111111111111111111111111	1111111111111111111111111111111111
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	1111111111101111111111111111111111	1111111111101111111111111111111111	1111111111101111111111111111111111
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	1111111111111111101111111111111110	1111111111111111101111111111111111	1111111111111111101111111111111111
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	1111111111111111111111111111111111	1111111111111111111111111111111111	1111111111111111111111111111111111
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	1111111111011111111111111111111111	1111111111011111111111111111111111	1111111111011111111111111111111110
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	1111111111111111111111111111111111	1111111111111111111111111111111111	1111111111111111111111111111111111
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	1111111111011111111111111111111111	1111111111011111111111111111111111	1111111111011111111111111111111111
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	1111111111111111111111111111111111	1111111111111111111111111111111111	1111111111111111111111111111111111
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	1111111111111111111111111111111111	1111111111111111111111111111111111	1111111111111111111111111111111111
test

Cell Formulas
Range	Formula
AT2:AT17	AT2	=TEXTJOIN("",,A2:AH2)
AU2:AU17	AU2	=TEXTJOIN("",,A2:AG2)&AI2
AV2:AV17	AV2	=TEXTJOIN("",,A2:AG2)&AJ2

 

[johnnyL]

I'm so confused as to what you are wanting to do.

 

[excelNewbie22]

hi johnny,
i want to check the first combination of zero's and one's, of 34 out of 41,
across 12.000 rows,
if there's a combination which repeats itself more then 10%, save it

then the second combination, then the third, all the way up to 22,000,000