6

I have three columns, each of which has different kinds of master data as shown below:

enter image description here

Now, I want to have all possible combinations of these three cells - like

aa kk jj
aa kk ff
aa ll jj
aa ll ff
aa mm jj
...

Can this be done with a formula. I found one formula with 2 columns, but i'm not able to extend it to 3 columns correctly

Formula with 2 columns:

=IF(ROW()-ROW($G$1)+1>COUNTA($A$2:$A$15)*COUNTA($B$2:$B$4),"",
INDEX($A$2:$A$15,INT((ROW()-ROW($G$1))/COUNTA($B$2:$B$4)+1))&
INDEX($B$2:$B$4,MOD(ROW()-ROW($G$1),COUNTA($B$2:$B$4))+1))

where G1 is the cell to place the resulting value

DPM
  • 1,960
  • 3
  • 26
  • 49
Ravi
  • 561
  • 2
  • 7
  • 19

1 Answers1

10

You can do this with a formula:

=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),"")

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • The formulas you create are a work of art. You really should have a museum named after you imho. xD – K.Dᴀᴠɪs Feb 06 '18 at 21:41
  • 3
    @K.Dᴀᴠɪs you are too kind, and there are people that put me to shame on a regular basis, I am just more prolific than they. – Scott Craner Feb 06 '18 at 21:48
  • need [help](https://stackoverflow.com/questions/56550006/how-to-list-all-possible-combinations-of-the-values-in-five-columns-in-excel) on the explanation/extension.. – p._phidot_ Jun 12 '19 at 10:43
  • For me, this formula creates only the first entry. The entries below are empty, even if I copy the formula there. Could you please offer an Excel file for download? – root May 21 '22 at 22:57