• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
shaowu459

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

Overview Reviews (1) Discussion
Excel Version
  1. 365
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
ABCDEF
1AUP1AppleA-UP-1-Apple
2BDOWN2OrangeA-UP-1-Orange
3C3PearA-UP-1-Pear
4D4A-UP-2-Apple
55A-UP-2-Orange
6A-UP-2-Pear
7A-UP-3-Apple
8A-UP-3-Orange
9A-UP-3-Pear
10A-UP-4-Apple
11A-UP-4-Orange
12A-UP-4-Pear
13A-UP-5-Apple
14A-UP-5-Orange
15A-UP-5-Pear
16A-DOWN-1-Apple
17A-DOWN-1-Orange
18A-DOWN-1-Pear
19A-DOWN-2-Apple
20A-DOWN-2-Orange
21A-DOWN-2-Pear
22A-DOWN-3-Apple
23A-DOWN-3-Orange
24A-DOWN-3-Pear
25A-DOWN-4-Apple
26A-DOWN-4-Orange
27A-DOWN-4-Pear
28A-DOWN-5-Apple
29A-DOWN-5-Orange
30A-DOWN-5-Pear
31B-UP-1-Apple
32B-UP-1-Orange
33B-UP-1-Pear
34B-UP-2-Apple
35B-UP-2-Orange
36B-UP-2-Pear
37B-UP-3-Apple
38B-UP-3-Orange
39B-UP-3-Pear
40B-UP-4-Apple
41B-UP-4-Orange
42B-UP-4-Pear
43B-UP-5-Apple
44B-UP-5-Orange
45B-UP-5-Pear
46B-DOWN-1-Apple
47B-DOWN-1-Orange
48B-DOWN-1-Pear
49B-DOWN-2-Apple
50B-DOWN-2-Orange
51B-DOWN-2-Pear
52B-DOWN-3-Apple
53B-DOWN-3-Orange
54B-DOWN-3-Pear
55B-DOWN-4-Apple
56B-DOWN-4-Orange
57B-DOWN-4-Pear
58B-DOWN-5-Apple
59B-DOWN-5-Orange
60B-DOWN-5-Pear
61C-UP-1-Apple
62C-UP-1-Orange
63C-UP-1-Pear
64C-UP-2-Apple
65C-UP-2-Orange
66C-UP-2-Pear
67C-UP-3-Apple
68C-UP-3-Orange
69C-UP-3-Pear
70C-UP-4-Apple
71C-UP-4-Orange
72C-UP-4-Pear
73C-UP-5-Apple
74C-UP-5-Orange
75C-UP-5-Pear
76C-DOWN-1-Apple
77C-DOWN-1-Orange
78C-DOWN-1-Pear
79C-DOWN-2-Apple
80C-DOWN-2-Orange
81C-DOWN-2-Pear
82C-DOWN-3-Apple
83C-DOWN-3-Orange
84C-DOWN-3-Pear
85C-DOWN-4-Apple
86C-DOWN-4-Orange
87C-DOWN-4-Pear
88C-DOWN-5-Apple
89C-DOWN-5-Orange
90C-DOWN-5-Pear
91D-UP-1-Apple
92D-UP-1-Orange
93D-UP-1-Pear
94D-UP-2-Apple
95D-UP-2-Orange
96D-UP-2-Pear
97D-UP-3-Apple
98D-UP-3-Orange
99D-UP-3-Pear
100D-UP-4-Apple
101D-UP-4-Orange
102D-UP-4-Pear
103D-UP-5-Apple
104D-UP-5-Orange
105D-UP-5-Pear
106D-DOWN-1-Apple
107D-DOWN-1-Orange
108D-DOWN-1-Pear
109D-DOWN-2-Apple
110D-DOWN-2-Orange
111D-DOWN-2-Pear
112D-DOWN-3-Apple
113D-DOWN-3-Orange
114D-DOWN-3-Pear
115D-DOWN-4-Apple
116D-DOWN-4-Orange
117D-DOWN-4-Pear
118D-DOWN-5-Apple
119D-DOWN-5-Orange
120D-DOWN-5-Pear
Sheet1
Cell Formulas
RangeFormula
F1:F120F1=REDUCE(A1:A4,B1:D1,LAMBDA(x,y,TOCOL(x&"-"&TOROW(OFFSET(y,,,99),1))))
Dynamic array formulas.


The second parameter of REDUCE function is B1:D1, so the function will calculate B1, C1, D1 one by one. Let's see what happend when calculating B1.

1. When calculating B1, y stands for B1, [OFFSET(B1,,,99)] returns range B1:B99 (0 for blank cells):

1668438676268.png


2. Use TOROW function transform values in B1:B99 to a row and ignore blanks:

1668438769570.png


3.The initial x is {"A";"B";"C";"D"} in column A so [x&"-"&TOROW(OFFSET(y,,,99),1)] returns:

1668438936846.png


4.Use TOCOL function tranform the result in step 3 to one column:

1668439000272.png


5.Above result becomes a new x in REDUCE function and will calculate with items in column C.

If you have more columns to add, just need to change the second parameter:

1668439461524.png
  • Like
Reactions: Jason5710, lrobbo314 and noalla
Author
shaowu459
Views
3,155
First release
Last update

Ratings

5.00 star(s) 1 ratings
Join the discussion

Share this Excel article

Latest reviews

E
just what I needed. Must easier than Power query: top
only thing: I need the result in three columns for further calcualtion.
Ednil
alias Jelle
shaowu459
shaowu459
Thanks for the rating :)
I don't know if I have understood you correctly. 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"?
Read more…

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top