counting combinations of 3's

[excelNewbie22]

hi, i got help here Excel Questions
regarding counting combinations of 2's,
now i need to do so for 3's, and 4's,
can someone help me modify it?

=LET(t,TOCOL(C2:H3),SUM(--(DROP(t,-1)&"-"&DROP(t,1)=C1:G1&"-"&D1:H1)))

and maybe explain it to me in a few words?

 

[coulombevin]

Hi excelNewbie22,

thanks for the explanation, I think I got something.

-English- *I have the french version of Excel, the formula might not be exact in english due to manual translation*

=LET(groupSize,3,
testRow,$I$1:$N$1,
tbl,A4:F7,
possibleOptionsFunction,LAMBDA(rowToConcat,groupSize,TEXTJOIN(";",VRAI,BYCOL(SEQUENCE(1,COLUMNS(rowToConcat)-groupSize+1,1,1),LAMBDA(c,TEXTJOIN("-",VRAI,CHOOSECOLS(rowToConcat,SEQUENCE(groupSize,1,MAX(c),1))))))),
allPossibilities,TEXTSPLIT(TEXTJOIN(";",TRUE,BYROW(tbl,LAMBDA(r,possibleOptionsFunction(r,groupSize)))),,";",TRUE),
testPossibilites,TEXTSPLIT(TEXTJOIN(";",TRUE,BYROW(testRow,LAMBDA(r,possibleOptionsFunction(r,groupSize)))),,";",TRUE),
uniqueTest,UNIQUE(testPossibilites),
SUM(BYROW(uniqueTest,LAMBDA(r,LAMBDA(t,SUM(IF(t="",0,ROWS(t))))(FILTER(allPossibilities,allPossibilities=r,""))))))

-French-

=LET(groupSize;3;
testRow;$I$1:$N$1;
tbl;A4:F7;
possibleOptionsFunction;LAMBDA(rowToConcat;groupSize;JOINDRE.TEXTE(";";VRAI;BYCOL(SEQUENCE(1;COLONNES(rowToConcat)-groupSize+1;1;1);LAMBDA(c;JOINDRE.TEXTE("-";VRAI;CHOISIRCOLS(rowToConcat;SEQUENCE(groupSize;1;MAX(c);1)))))));
allPossibilities;FRACTIONNER.TEXTE(JOINDRE.TEXTE(";";VRAI;BYROW(tbl;LAMBDA(r;possibleOptionsFunction(r;groupSize))));;";";VRAI);
testPossibilites;FRACTIONNER.TEXTE(JOINDRE.TEXTE(";";VRAI;BYROW(testRow;LAMBDA(r;possibleOptionsFunction(r;groupSize))));;";";VRAI);
uniqueTest;UNIQUE(testPossibilites);
SOMME(BYROW(uniqueTest;LAMBDA(r;LAMBDA(t;SOMME(SI(t="";0;LIGNES(t))))(FILTRE(allPossibilities;allPossibilities=r;""))))))

As you can see in the minisheet:

Classeur1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	7	9	17	29	30	33	1		5	6	55	2	57	1
2	7	12	13	27	34	36
3	2	5	6	55	57	59
4	7	9	17	29	30	33	3
5	7	2	57	1	30	33
6	7	12	13	2	57	1
7	2	5	6	55	57	59
Feuil3

Cell Formulas
Range	Formula
G1	G1	=LET(groupSize,3, testRow,$I$1:$N$1, tbl,A1:F3, possibleOptionsFunction,LAMBDA(rowToConcat,groupSize,TEXTJOIN(";",TRUE,BYCOL(SEQUENCE(1,COLUMNS(rowToConcat)-groupSize+1,1,1),LAMBDA(c,TEXTJOIN("-",TRUE,CHOOSECOLS(rowToConcat,SEQUENCE(groupSize,1,MAX(c),1))))))), allPossibilities,TEXTSPLIT(TEXTJOIN(";",TRUE,BYROW(tbl,LAMBDA(r,possibleOptionsFunction(r,groupSize)))),,";",TRUE), testPossibilites,TEXTSPLIT(TEXTJOIN(";",TRUE,BYROW(testRow,LAMBDA(r,possibleOptionsFunction(r,groupSize)))),,";",TRUE), uniqueTest,UNIQUE(testPossibilites), SUM(BYROW(uniqueTest,LAMBDA(r,IFERROR(ROWS(FILTER(allPossibilities,allPossibilities=r)),0)))))
G4	G4	=LET(groupSize,3, testRow,$I$1:$N$1, tbl,A4:F7, possibleOptionsFunction,LAMBDA(rowToConcat,groupSize,TEXTJOIN(";",TRUE,BYCOL(SEQUENCE(1,COLUMNS(rowToConcat)-groupSize+1,1,1),LAMBDA(c,TEXTJOIN("-",TRUE,CHOOSECOLS(rowToConcat,SEQUENCE(groupSize,1,MAX(c),1))))))), allPossibilities,TEXTSPLIT(TEXTJOIN(";",TRUE,BYROW(tbl,LAMBDA(r,possibleOptionsFunction(r,groupSize)))),,";",TRUE), testPossibilites,TEXTSPLIT(TEXTJOIN(";",TRUE,BYROW(testRow,LAMBDA(r,possibleOptionsFunction(r,groupSize)))),,";",TRUE), uniqueTest,UNIQUE(testPossibilites), SUM(BYROW(uniqueTest,LAMBDA(r,IFERROR(ROWS(FILTER(allPossibilities,allPossibilities=r)),0)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I4:I30	Cell Value	duplicates	text	NO

In G1 & G4, i have the formula that look at the test row (I1:N1) and find the double values (5-6-55 and 2-57-1) (6-55-2,55-2-57 not found in tbl).

If you want to reduce the formula size, i would go in FORMULA > NAME MANAGER and create a new one (eg. countDuplicates) and I would put the following formula:

=LAMBDA(groupSize,testRow,rangeToTest,LET(groupSize,groupSize,
testRow,testRow,
tbl,rangeToTest,
possibleOptionsFunction,LAMBDA(rowToConcat,groupSize,TEXTJOIN(";",VRAI,BYCOL(SEQUENCE(1,COLUMNS(rowToConcat)-groupSize+1,1,1),LAMBDA(c,TEXTJOIN("-",VRAI,CHOOSECOLS(rowToConcat,SEQUENCE(groupSize,1,MAX(c),1))))))),
allPossibilities,TEXTSPLIT(TEXTJOIN(";",TRUE,BYROW(tbl,LAMBDA(r,possibleOptionsFunction(r,groupSize)))),,";",TRUE),
testPossibilites,TEXTSPLIT(TEXTJOIN(";",TRUE,BYROW(testRow,LAMBDA(r,possibleOptionsFunction(r,groupSize)))),,";",TRUE),
uniqueTest,UNIQUE(testPossibilites),
SUM(BYROW(uniqueTest,LAMBDA(r,LAMBDA(t,SUM(IF(t="",0,ROWS(t))))(FILTER(allPossibilities,allPossibilities=r,"")))))))

And i would call the formula like this in cells: =countDuplicates(3,$I$1:$N$1,tbl;A4:F7)

Bests regards,

Vincent

 

[kvsrinivasamurthy]

Single formula in Q167, for result in Q column. No helper columns.

=LET(a,C167:H174,b,DROP(a,1,-2),c,DROP(DROP(a,1,1),0,-1),d,DROP(a,1,2),za,MAKEARRAY(ROWS(a)-1,COLUMNS(a)-2,LAMBDA(roa,cla,SUMPRODUCT((DROP(b,roa-1)=INDEX(a,roa,cla))*(DROP(c,roa-1)=INDEX(a,roa,cla+1))*(DROP(d,roa-1)=INDEX(a,roa,cla+2))))),zc,BYROW(za,LAMBDA(rob,SUM(rob))),zc)

 

[excelNewbie22]

Vincent this is perfect!
thanks a lot!

(didn't workout the formula shorting though)

kvsrinivasamurthy
what you gave is checking for triples of 3's in one range, and i needed to check from 1 range into another

 

[kvsrinivasamurthy]

This is not for single range. This gives result of column Q as explained in post#7.

 

[excelNewbie22]

i see it now, but still this isn't good for me, because i need to be able to modify the formula for checking against another range in different columns,
anyway, i really do appreciate you trying to help me, thank you

 

[coulombevin]

Vincent this is perfect!
thanks a lot!

(didn't workout the formula shorting though)

kvsrinivasamurthy
what you gave is checking for triples of 3's in one range, and i needed to check from 1 range into another

Hi excelNewbie22,

Sorry for the shorting, it's my mistake, here's the appropriate formula wihtout variable with the same name:

=LAMBDA(groupSize,testRow,rangeToTest,LET(tbl,rangeToTest,
possibleOptionsFunction,LAMBDA(rowToConcat,groupSize,TEXTJOIN(";",VRAI,BYCOL(SEQUENCE(1,COLUMNS(rowToConcat)-groupSize+1,1,1),LAMBDA(c,TEXTJOIN("-",VRAI,CHOOSECOLS(rowToConcat,SEQUENCE(groupSize,1,MAX(c),1))))))),
allPossibilities,TEXTSPLIT(TEXTJOIN(";",TRUE,BYROW(tbl,LAMBDA(r,possibleOptionsFunction(r,groupSize)))),,";",TRUE),
testPossibilites,TEXTSPLIT(TEXTJOIN(";",TRUE,BYROW(testRow,LAMBDA(r,possibleOptionsFunction(r,groupSize)))),,";",TRUE),
uniqueTest,UNIQUE(testPossibilites),
SUM(BYROW(uniqueTest,LAMBDA(r,LAMBDA(t,SUM(IF(t="",0,ROWS(t))))(FILTER(allPossibilities,allPossibilities=r,"")))))))

This way you should be able to save as a formula (eg. countDuplicate).

Bests regards,

Vincent