0

I am looking to combine values of all possible combinations over 9 columns instead of the 2 that I have the code for which sits in the format of:

Aa  aabb
BB  bbaa
Cc  AACC 
       Ccbb 
        .....
        ......

Could someone please save my life and update this too go over 9 not 2 columns.

This is the code I used for 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))
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
Nick Gilbert
  • 1
  • How set are you on formulas? As this is very doable through VBA. I'm sure multiple examples can be found around here. Furthermore, [powerquery](https://www.quora.com/How-can-I-generate-a-list-of-all-possible-combinations-from-3-or-more-columns-of-data-in-Microsoft-Excel) is another option – JvdV Aug 15 '19 at 10:53
  • It's for work and we use excel, so unfortunately very set on them – Nick Gilbert Aug 15 '19 at 11:28
  • Both VBA and powerquery come with Excel – JvdV Aug 15 '19 at 11:29
  • Awesome thanks mate, I'll look into it – Nick Gilbert Aug 15 '19 at 11:35
  • Consider even SQL with [cross join query](https://stackoverflow.com/a/31632391/1422451) to render cartesian product: `SELECT Col1, Col2, ..., Col9 FROM [MySheet$A1:A###], [MySheet$B1:B###], ..., [MySheet$I1:I###]` – Parfait Aug 15 '19 at 18:21

0 Answers0