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

shaowu459 · Nov 14, 2022

shaowu459 submitted a new Excel article:

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

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

Excel Formula:

=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 · Aug 24, 2023

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 · Aug 24, 2023

ednil said:
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 · Aug 24, 2023

thx

Jelle

shaowu459 · Aug 24, 2023

ednil said:
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 · Aug 24, 2023

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 · Aug 25, 2023

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 · Aug 25, 2023

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 · Aug 25, 2023

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.

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

shaowu459

Well-known Member

Excel Facts

ednil

New Member

shaowu459

Well-known Member

ednil

New Member

shaowu459

Well-known Member

shaowu459

Well-known Member

ednil

New Member

shaowu459

Well-known Member

shaowu459

Well-known Member

Forum statistics

Share this page

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

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock