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,

Do you have an example of data so we could better understand the problem?

Thanks,

Vincent

 

[excelNewbie22]

didn't noticed for posting the wrong link

Using two Vstacks within countif?

hi, i'm trying to count combinations of two's from 2 ranges, and want to put it all in one formula rather using helper columns, =SUM(COUNTIF(vstack(C3&"-"&D3,D3&"-"&E3,E3&"-"&F3,F3&"-"&G3,G3&"-"&H3...

www.mrexcel.com

any how... i need the q2 result in one formula like in the post above with no helpers columns, and also same for repeating of 4's
sample:

149.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1															SUM
2	2	4	6	8	10	12	2-4-6	4-6-8	6-8-10	8-10-12	1	1	0	0	2
3	1	2	4	6	8	40	1-2-4	2-4-6	4-6-8	6-8-40
12

Cell Formulas
Range	Formula
I2:L3	I2	=C2&"-"&D2&"-"&E2
M2	M2	=COUNTIF(I3:L4,I2)
N2	N2	=COUNTIF(I3:L4,J2)
O2	O2	=COUNTIF(I3:L4,K2)
P2	P2	=COUNTIF(I3:L4,L2)
Q2	Q2	=SUM(M2:P2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:P2,T5:W2023,M4:P4	Cell Value	=2	text	NO
M2:P2,T5:W2024,M4:P4	Cell Value	=1	text	NO

 

[coulombevin]

Hi,

If you consider column "I:L" as helper, then I will need some more time to think about it.

If not, here's how I would do it:
- for each row of column "I", I would add the following formula with addapted range (C2:H2) and the number of combination elements (3) :

=LAMBDA(rowToConcat,groupSize,BYCOL(SEQUENCE(1,COLUMNS(rowToConcat)-groupSize+1,1,1),LAMBDA(c,TEXTJOIN("-",TRUE,CHOOSECOLS(rowToConcat,SEQUENCE(groupSize,1,MAX(c),1))))))(C2:H2,3)

- In the cell where you want the sum of duplication and change the range to fit the data obtained by the previous step:

=LAMBDA(tbl,SUM(BYROW(UNIQUE(TOCOL(tbl)),LAMBDA(r,COUNTIF(tbl,r)-1))))(I2:L4)

Let me know if something need to be changed.

Vincent

 

[excelNewbie22]

thanks coulombevin but i need "one for all" formula

 

[coulombevin]

Hi excelNewbie22,

Here's my new try, you just have to change "groupSize" (3) for the value that you need (3's), and "tbl" for the range with the number.

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

=LET(groupSize,3,
tbl,C8:H11,
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),
uniquePossib,UNIQUE(allPossibilities),
SUM(BYROW(uniquePossib,LAMBDA(r,ROWS(FILTER(allPossibilities,allPossibilities=r,""))-1))))

-French-

=LET(groupSize;3;
tbl;C8:H11;
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);
uniquePossib;UNIQUE(allPossibilities);
SOMME(BYROW(uniquePossib;LAMBDA(r;LIGNES(FILTRE(allPossibilities;allPossibilities=r;""))-1))))

Bests regards,

Vincent

 

[excelNewbie22]

thanks vincent, but several things don't working out for me, i'll try explaining with a sample

333.xlsb
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
167	7	9	17	29	30	33	7-9-17	9-17-29	17-29-30	29-30-33	0	0	0	0	0	1
168	7	12	13	27	34	36	7-12-13	12-13-27	13-27-34	27-34-36	0	0	0	0	0	1
169	7	11	12	21	36	37	7-11-12	11-12-21	12-21-36	21-36-37	0	0	0	0	0	1
170	1	16	17	20	21	27	1-16-17	16-17-20	17-20-21	20-21-27	0	0	0	0	0	1
171	8	9	19	24	29	34	8-9-19	9-19-24	19-24-29	24-29-34	1	0	0	0	1	1
172	7	8	9	19	21	33	7-8-9	8-9-19	9-19-21	19-21-33	0	0	0	0	0	0
173	1	13	14	23	29	33	1-13-14	13-14-23	14-23-29	23-29-33	0	0	0	0	0	0
1212

Cell Formulas
Range	Formula
I167:L173	I167	=C167&"-"&D167&"-"&E167
M167:M173	M167	=COUNTIF(I168:L173,I167)
N167:N173	N167	=COUNTIF(I168:L173,J167)
O167:O173	O167	=COUNTIF(I168:L173,K167)
P167:P173	P167	=COUNTIF(I168:L173,L167)
Q167:Q173	Q167	=SUM(M167:P167)
R167:R173	R167	=LET(groupSize,3, tbl,C167:H172, 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), uniquePossib,UNIQUE(allPossibilities), SUM(BYROW(uniquePossib,LAMBDA(r,ROWS(FILTER(allPossibilities,allPossibilities=r,""))-1))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M:P	Cell Value	>0	text	NO
Q:R	Cell Value	>0	text	NO

first thing, i need the formula to point to 2 ranges, one point for the test row (like c167:h167) agains the the second range c168:h173, cause going forward i'll need the change 1 of the two ranges,

second, in R167 it says i have one duplicate, which i really don't, like i show in m167: p167 which is the long way, with helpers, for this formula

third, and i know i can't really ask for it, but i'll try, is there a chance for a shorter formula? i'm intending to run it for like a million rows (testing data)

 

[coulombevin]

Hi,

1- When you said you need "one for all" formula, what does it mean if not calculate all double values?
2- What is the test row? Is it like the values you want to find in the million rows where you write multiple number (eg. 1,3,4,5) or multiple 3's and 4's (eg. 1-3-4,3-4-5,...)?
3- R167 is right, it looks if there is double values in the range (C167:H172) and you have 8-9-19 from C-D-E171 and D-E-F172. What is wrong here and what is supposed to be the correct answer?
4- I will try but i think i'm lost in your project sorry

Thanks for the precision,

Vincent

 

[kvsrinivasamurthy]

Helper columns M,N,O,P can be avoided using only these two spill out formulas.
In I167

=LET(a,C167:H173,MAKEARRAY(ROWS(a),COLUMNS(a)-2,LAMBDA(r,c,INDEX(a,r,c)&"-"&INDEX(a,r,c+1)&"-"&INDEX(a,r,c+2))))

In Q167

=MAP(I167:I173,I168:I174,L167:L173,LAMBDA(a,b,c,SUM(COUNTIF(b:L173,a:c))))

 

[excelNewbie22]

kvsrinivasamurthy, thank you, i liked the first formula, but still, i'm looking for one formula, that is, if it's possible (like the sample in post # 1)

vincent,
1- i meant without any helper columns
2-test row will be in seperate column, like say, $c$1:$h$1 (i.e 1-20-30-35-55-79)
BUT for now i'm testing one row against the 6 rows after, like in the sample above
3-you right, but it isn't what i ask, R167 is checking for doubles (of 3's) in range C167:H172, but i asked for finding doubles from C166:H166
4-no need for sorry, you just trying to help, and i appreciate it