CODEWITHSUNDEEP
excel-formula
John Leonard
John Leonard

Reputation: 11

How to generate all combinations using 5 columns using a formula in Excel?

I saw and used the formula for combinations for 3 columns. I have been trying to extend it to 4 columns and then to 5 columns. I can't get it to work properly. I don't want to use a table. This was the original formula I found on this site posted by Scott Craner. I expanded on it for 5 columns but can't get it to work properly.

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

Upvotes: 1

Views: 7060

Answers (2)

mark fitzpatrick
mark fitzpatrick

Reputation: 3310

Here is an extensible formula that can generate all combinations from an array input of up to five columns.

Caveats:

  • It requires Excel 2019 or Excel 365
  • It does not take columns as inputs - it takes an array, so you must size the input array to fit the columns that you want. (pls see notes below)
  • While it does not rely on volatile functions, it is still computationally intensive and can run slow.
  • You must have a minimum of 2 columns in your range and a maximum of 5.
  • No column in the array can be completely blank.

In this example, I am taking an array of text cells in the range A1:E5 and I will combine them with a delimiter of " ". The formula uses LET to make it more efficient and readable. You can copy paste it into your destination cell - the formatting is only there to make it readable:

=LET( matrix, A1:E5,
      d, " ",
       cC, COLUMNS( matrix ), cSeq, SEQUENCE( 1, cC ),
       rC, ROWS( matrix ), rSeq, SEQUENCE( rC ),
       eC, rC ^ cC, eSeq, SEQUENCE( eC,,0 ),
       m, INDEX( matrix, MOD( INT( INT( SEQUENCE( eC, cC, 0 )/cC )/rC^SEQUENCE( 1, cC, cC-1, -1 ) ), rC ) + 1, cSeq ),
       n, INDEX( m, eSeq+1, cSeq ) <> 0,
       r, IFERROR(INDEX( m, eSeq  + 1, 1 ), "") &d& INDEX( m, eSeq  + 1, 2 ) &d& IFERROR(INDEX( m, eSeq  + 1, 3 ), "") &d&
           IFERROR(INDEX( m, eSeq  + 1, 4 ), "") &d& IFERROR(INDEX( m, eSeq  + 1, 5 ), ""),
       o, IFERROR( INDEX( r, eSeq+1, ), n ),
       FILTER( INDEX( o, , 1), INDEX( n, , 1 ) * INDEX( n, , 2 ) * IFERROR(INDEX( n, , 3 ), TRUE) *
               IFERROR(INDEX( n, , 4 ), TRUE) * IFERROR(INDEX( n, , 5 ), TRUE) ) )

Set the variable matrix to the range that you want. It is currently set to A1:E5. Set the d variable to the delimiter that you want to use. It is set to " " or space right now. Here is an example of the output:

sample output

Notes

I have another version that will take columns, but it is failing and I won't have time to debug it, so I thought it would be better to give you something for now that you might be able to adapt to your needs. The other version is also computationally faster. When I have debugged it, I will post it here.

This is extensible. If you want it to go to 6, 7, or more columns, it can be pretty easily modified. I chose not to do that because each column that it processes (even if there are no data in the column) slows down the calculation because of the design that I used. To extend it, you need to edit two lines near the end:

  1. You have to edit the variable r. You need to append another &d&IFERROR(INDEX(m,eSeq+1, XXX ),"") in front of the comma where XXX is the next column. For example 6 and then 7, etc.
  2. You have to edit the final result that starts with FILTER. You need to append another *IFERROR(INDEX(n,, XXX ),TRUE) in front of the last two close parens where XXX is the next column. For example 6 and then 7, etc.

Here is an example of a 7 column version:

=LET( matrix, A1:G5,
       d, " ",
       cC, COLUMNS( matrix ), cSeq, SEQUENCE( 1, cC ),
       rC, ROWS( matrix ), rSeq, SEQUENCE( rC ),
       eC, rC ^ cC, eSeq, SEQUENCE( eC,,0 ),
       m, INDEX( matrix, MOD( INT( INT( SEQUENCE( eC, cC, 0 )/cC )/rC^SEQUENCE( 1, cC, cC-1, -1 ) ), rC ) + 1, cSeq ),
       n, INDEX( m, eSeq+1, cSeq ) <> 0,
       r, IFERROR(INDEX( m, eSeq  + 1, 1 ), "") &d& INDEX( m, eSeq  + 1, 2 ) &d& IFERROR(INDEX( m, eSeq  + 1, 3 ), "") &d&
           IFERROR(INDEX( m, eSeq  + 1, 4 ), "") &d& IFERROR(INDEX( m, eSeq  + 1, 5 ), "") &d& IFERROR(INDEX( m, eSeq  + 1, 6 ), "") &d& IFERROR(INDEX( m, eSeq  + 1, 7 ), ""),
       o, IFERROR( INDEX( r, eSeq+1, ), n ),
       FILTER( INDEX( o, , 1), INDEX( n, , 1 ) * INDEX( n, , 2 ) * IFERROR(INDEX( n, , 3 ), TRUE) *
               IFERROR(INDEX( n, , 4 ), TRUE) * IFERROR(INDEX( n, , 5 ), TRUE) * IFERROR(INDEX( n, , 6 ), TRUE) * IFERROR(INDEX( n, , 7 ), TRUE) ) )

Upvotes: 2

Mark S.
Mark S.

Reputation: 2584

Say your data is in the first row for each column, you can use the following formulas to combine them. & =Concat =Concatenate

You can also add in custom text if you include " " in your formulas.

So you might use the formula like so:

=A1 & B1 & C1 & D1 & E1 or you can do something like =A1 & " " & B1 if you're trying to add a space between them.

Similarly, =Concat(A1,B1,C1,D1,E1) / =Concat(A1," ",B1)

Lastly, =Concatenate(A1,B1,C1,D1,E1) / =Concatenate(A1," ",B1)

Upvotes: 0

Related Questions