Excel Formula: An easy way to get all combinations of items in different columns

[shaowu459]

shaowu459 submitted a new Excel article:

Excel Formula: An easy way to get all combinations of items in different columns - Office 365, Reduce, Lambda

Using REDUCE function in Office 365 we can get combinations of items in different columns very easily.

=REDUCE(A1:A4,B1:D1,LAMBDA(x,y,TOCOL(x&"-"&TOROW(OFFSET(y,,,99),1))))

REDUCE.xlsx
	A	B	C	D	E	F
1	A
Sheet1

[XR][XD=h:c|ff:ari|fz:18pt|cls:bl bt br...[/XR]

Read more about this Excel article...

 

[ednil]

Do you mean to list the contents of the string “A-UP-1” in three cells, such as A1="A", B1="UP", and C1="1"

yes that's what i want
sorry
but do not know how to reply to your reaccrion

thx

Jelle

 

[shaowu459]

Do you mean to list the contents of the string “A-UP-1” in three cells, such as A1="A", B1="UP", and C1="1"

yes that's what i want
sorry
but do not know how to reply to your reaccrion

thx

Jelle

it is possible, i write the formula before, i will check it tomorrow.

 

[ednil]

thx

Jelle

 

[shaowu459]

thx

Jelle

for your reference, however, if you have a large dataset, i'm not sure whether the efficiency is acceptable.

Book.xlsx
	A	B	C	D	E	F	G	H	I	J
1	A	D	F	O		A	D	F	O
2	B	E	G			A	D	G	O
3	C		H			A	D	H	O
4			I			A	D	I	O
5						A	E	F	O
6						A	E	G	O
7						A	E	H	O
8						A	E	I	O
9						B	D	F	O
10						B	D	G	O
11						B	D	H	O
12						B	D	I	O
13						B	E	F	O
14						B	E	G	O
15						B	E	H	O
16						B	E	I	O
17						C	D	F	O
18						C	D	G	O
19						C	D	H	O
20						C	D	I	O
21						C	E	F	O
22						C	E	G	O
23						C	E	H	O
24						C	E	I	O
25
Sheet1

Cell Formulas
Range	Formula
F1:I24	F1	=REDUCE(A1:A3,B1:D1,LAMBDA(x,y,LET(s,TOCOL(OFFSET(y,,,99),1),DROP(REDUCE(0,SEQUENCE(ROWS(x)),LAMBDA(m,n,LET(t,INDEX(x,n,),VSTACK(m,IFNA(HSTACK(t,s),t))))),1))))
Dynamic array formulas.

 

[shaowu459]

Book.xlsx
	A	B	C	D	E	F	G	H	I	J
1	A	D	F	O		A	D	F	O
2	B	E	G			A	D	G	O
3	C		H			A	D	H	O
4			I			A	D	I	O
5						A	E	F	O
6						A	E	G	O
7						A	E	H	O
8						A	E	I	O
9						B	D	F	O
10						B	D	G	O
11						B	D	H	O
12						B	D	I	O
13						B	E	F	O
14						B	E	G	O
15						B	E	H	O
16						B	E	I	O
17						C	D	F	O
18						C	D	G	O
19						C	D	H	O
20						C	D	I	O
21						C	E	F	O
22						C	E	G	O
23						C	E	H	O
24						C	E	I	O
25
Sheet1

Cell Formulas
Range	Formula
F1:I24	F1	=REDUCE(A1:A3,B1:D1,LAMBDA(x,y,DROP(REDUCE(0,SEQUENCE(ROWS(x)),LAMBDA(m,n,LET(t,INDEX(x,n,),VSTACK(m,IFNA(HSTACK(t,TOCOL(OFFSET(y,,,99),1)),t))))),1)))
Dynamic array formulas.

 

[ednil]

thx a lot
i need this for solving binairo/takuzu puzzle
to test for solution i have manipulate each combination further
Now on short holiday so next week i will continue

if memory/speed is a problem than i will fall back on using mixed radix system for generating combinations
I am amazed, after having used excel in the past , about dynamic arrays, lamnda, let statement
is for me as die hard programming in Fortran, Pscal, Algol,Ada,Python etc a learning curve to be able to understand your very nice piece of code
thanks a lot

Jelle

 

[shaowu459]

Thank you for your response. Wishing you a pleasant vacation. In Office 365, lambda functions can be used by defining a name and then solving some classic problems using recursion, such as the Hanoi Tower problem. Here is the recursive formula that I have written before to solve the Hanoi Tower problem for your reference:

 

[shaowu459]

I think both the Binairo/Takuzu puzzle and Sudoku problems can be solved using a recursive method. However, I have never tried it in Excel. Through my testing in Excel, there is a certain limit on the depth of loops for recursion, and efficiency becomes an issue when dealing with large amounts of data, far worse than other real programming languages. Another issue is that Excel can only handle up to one million rows, and problems like m-n or n-permutations of n numbers easily result in results exceeding one million rows.