Creating Comibinaation

[patel18nik]

Hello,

I am trying to creating possible combination for five columns and I am using below formula which is for 3 columns only, I am trying add 2 more columns but its not letting me or formula is not right?

=IFERROR(INDEX($A$1:$A$3,INT((ROW(1:1)-1)/((COUNTA($B$1:$B$2)*(COUNTA($C$1:$C$3)))))+1)&" "&INDEX($B$1:$B$2,MOD(INT((ROW(1:1)-1)/COUNTA($C$1:$C$3)),COUNTA($B$1:$B$2))+1)&"-"&INDEX($C$1:$C$3,MOD((ROW(1:1)-1),COUNTA($C$1:$C$3))+1),"")

In this formula: A2:A4, B2:B6, C2:C5 are the data ranges that has been used but I wanna use column D and E too.

Thank You,
Nikhil Patel

 

[StephenCrump]

Welcome to the Forum!

With Excel 365's dynamic functions, you could do it like this:

	A	B	C	D	E	F	G	H	I	J	K	L
1		A	B	C	D	E
2		A1	B1	C1	D1	E1		A1	B1	C1	D1	E1
3		A2	B2	C2	D2	E2		A1	B1	C1	D1	E2
4		A3	B3	C3	D3	E3		A1	B1	C1	D1	E3
5				C4	D4			A1	B1	C1	D2	E1
6				C5				A1	B1	C1	D2	E2
7								A1	B1	C1	D2	E3
8								A1	B1	C1	D3	E1
9	ColTotals	3	3	5	4	3		A1	B1	C1	D3	E2
10	NoRows	180	60	12	3	1		A1	B1	C1	D3	E3
11								A1	B1	C1	D4	E1
12								A1	B1	C1	D4	E2
13								A1	B1	C1	D4	E3
14								A1	B1	C2	D1	E1
15								A1	B1	C2	D1	E2
16								A1	B1	C2	D1	E3
17								A1	B1	C2	D2	E1
Sheet1

Cell Formulas
Range	Formula
H2:L541	H2	=INDEX(MyData,MOD(INT(SEQUENCE(PRODUCT(ColTotals),,0)/NoRows),ColTotals)+1,SEQUENCE(,COLUMNS(MyData)))
B9:F9	B9	=MMULT(SEQUENCE(,ROWS(MyData),,0),--NOT(ISBLANK(MyData)))
B10:F10	B10	=IF(COLUMN()=MAX(COLUMN(MyData)),1,C10*C9)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
ColTotals	=Sheet1!$B$9#	H2
MyData	=Sheet1!$B$2:$F$6	H2, B10:F10, B9
NoRows	=Sheet1!$B$10:$F$10	H2