0

I want to generate all possible combinations for unique values in n columns. Please consider the scenario below:

In one sheet i am taking an input from the user where he/she can input the number of columns for which they want to generate the combinations and then can enter the unique values in each of the columns. Let's say that the user entered 10 as input, he will automatically get 10 input columns as below:

User input where column values need to be entered

Now, in each of the columns, user will input the variable name and the unique values of that variable. Once this is done, they will click on a button, which will generate all possible combinations for these n variables in a separate tab.

Need your help on how to generate these combinations using vba, considering n can vary upto 50.

Thanks!

braX
  • 11,506
  • 5
  • 20
  • 33
Rohit Lodha
  • 43
  • 8
  • Do you have some idea of how many combinations you would end up with given 50 lists each of two values? – Tim Williams Oct 03 '19 at 02:54
  • 1
    Previously: https://stackoverflow.com/questions/19780016/vba-write-all-possible-combinations-of-4-columns-of-data/19780307#19780307 – Tim Williams Oct 03 '19 at 02:58
  • @TimWilliams - I understand that it will be more than what excel will be able to handle. But, 45 columns can have a single value with the other 5 having...let's say 2 values. Always remember, Use cases are industry specific. Also, thanks for your answer. – Rohit Lodha Oct 03 '19 at 15:44

1 Answers1

0

Here is part 1 of 2 (a simple loop based on input with formatting). In the next step, the user would enter the data and maybe someone else can provide you part 2 of the macro!:

Sub VarCount()

Dim inp As Variant
inp = InputBox("How many variables?")

If IsNumeric(inp) And inp > 0 And inp <= 50 Then

    Range("A1").Value = "Var Count"
    Range("A1").Font.Bold = True
    Range("A1").Font.Italic = True

    Range("A2").Value = "Var Name"
    Range("A2").Font.Bold = True
    Range("A2").Font.Italic = True


    For i = 2 To inp + 1:
        Cells(1, i).Value = i - 1
        Cells(1, i).HorizontalAlignment = xlCenter
    Next

Else: MsgBox ("Please enter an integer between 1 and 50 and re-run the macro.")


End Sub
David Erickson
  • 16,433
  • 2
  • 19
  • 35