Today we are going to discuss about numerous methods in Excel on how to generate possible combinations from group of numbers.
Here is our Input data and our expected result.
Method-1:
Formula in E2 cell,
=TOCOL(TOCOL(A2:A4&", "&TOROW(B2:B7))&", "&TOROW(C2:C3))
Let me start this from scratch,
if we refer B2:B7,
={8;9;10;11;12;13}
This is 6 row and 1 column array, let me wrap this array in TOROW function and see the output.
={8,9,10,11,12,13}
This is 1 row and 6 column array, we converted column into row using TOROW function.
Concatenate A2:A4 and the result of TOROW function with ", " as delimiter.
To make this a single column array wrap this inside TOCOL function and again concatenate the result of TOCOL function with delimiter ", " and TOROW(C2:C3).
Now, all the combinations are there but in 2 columns. If we wrap this array into TOCOL function, we will get one column array of all combinations.
Method-2:
=LET(x,A2:C7,MID(REDUCE("",SEQUENCE(COLUMNS(x)),LAMBDA(a,v,TOCOL(a &", "&TOROW(INDEX(x,,v),3),3))),3,50))
In first method we used TOCOL and TOROW function 2 times, since we have 3 groups.
If the number of groups increase, then we need to increase the occurrence of these functions. To make it more dynamic, we can iterate based on number of columns using REDUCE function and use TOCOL and TOROW functions once.
In REDUCE function,
initial value -> ""
array -> Sequence of numbers based on number of Groups.
function -> Inside LAMBDA function, we declare 2 variables,
a -> accumulator,
v -> Current value,
in first iteration,
a -> "" (Initial value)
v -> 1
INDEX(x,,v) will pick first column from the range A2:C7, TOROW function will help us to make it a 1 row 6 column array; to ignore blanks and error values we can feed 3 in 3rd argument in TOROW function.
The result is shown below,
{", 1",", 2",", 3"}
TOCOL function will make this, 3 row and 1 column array. Again, to ignore blanks and error values we feed 3 in 3rd argument.
in 2nd iteration,
a -> {", 1";", 2";", 3"}
v -> 2
The steps in first iteration will be carried out here, but with 2nd column of our input range and our accumulator.
At the end of 3rd iteration, we will get out expected output but with ", " at beginning for each item. MID function will help us to start from 3rd character till end of text. In that way,
we can eliminate ", " at the beginning.
Comments