0

Is there a way to create a macro in excel to generate all possible combinations (Cartesian product) of a number of sets (more than 8), each containing a dynamic number of words (up to 60 words).

eg. if I have 4 sets as below:

  • Set 1: A1
  • Set 2: B1, B2, B3, B4
  • Set 3: C1, C2
  • Set 4: D1, D2

It should create 16 combinations: (A1 B1 C1 D1), (A1 B1 C2 D1), (A1 B1 C1 D2), (A1 B1 C2 D2), (A1 B2 C1 D1), (A1 B2 C2 D1), (A1, B2 C1 D2), (A1 B2 C2 D2), (A1 B3 C1 D1), (A1 B3 C2 D1), (A1 B3 C1 D2), (A1 B3 C2 D2), (A1 B4 C1 D1), (A1 B4 C2 D1), (A1 B4 C1 D2), (A1 B4 C2 D2) -Please see the picture

The above example is easy to make manyally. But for the below one, it is supposed to create 3456 combinations

  • Set 1 A1
  • Set 2 B1 B2 B3 B4
  • Set 3 C1 C2
  • Set 4 D1 D2 D3 D4
  • Set 5 E1 E2
  • Set 6 F1 F2 F3 F4 F5 F6
  • Set 7 G1 G2 G3
  • Set 8 H1 H2 H3

Any idea how to do that for dynamic number of sets with dynamic number of words? Thanks A Lot.

MZee
  • 1
  • 1
  • Yes you can create this using VBA. Give it a Try yourself, if it doesn't work then you can post the non-working code here. You can start by learning about `Array` & `loop` in VBA. – Mikku Jul 31 '19 at 06:43
  • You can find a couple of different solutions [here](https://stackoverflow.com/questions/31472816). – BrakNicku Jul 31 '19 at 07:17
  • @mikku I couldn't really get a firm grip on Arrays, but I understand the loop concept. So I ended up making it work using just loops. I Tested the macro up to 3456 combinations and compared it to ones I created manually. It worked perfectly. At first the loops were a bit repetitive, so it took around 3 mins to create the combinations, but then i changed it a bit, it takes less than 5 seconds now. Thank you for advising me to try it myself ... I have been trying for the last 3 days with no results. – MZee Jul 31 '19 at 16:28
  • @BrakNicku Thanks man for the suggested solutions ... am so novice in VBA, I couldn't understand the Array part completely, and how others name their variables. But as you can see above. I eventually was able to do it just using loops. – MZee Jul 31 '19 at 16:33

0 Answers0