0

I need to take an input of mins and maxes for multiple variables and generate an array containing each possible combination.

Example: Entering the array 

[A min, A max        
 B min, B max]

should return

[A min, B min      
 A min, B max                                               
 A max, B min                                   
 A max, B max]

I was able to do this but only with under 3 variables but can't conveniently expand it. I can't figure out how to make it work for any amount of variables, like if there was a C that also has a max and min.

Does anyone have suggestions?

edit: If this helps anyone, purpose of this function is to find the extremes of a variable based expression. The first array is generated from the variables included in the expression, then the variables are replaced with values from the second array. So essentially every is calculated to find the highest possible outcome and lowest possible outcome.

So an input that created the first array could have been something like: 'A+B' Then, for each row in the second array, 'A' and 'B' would be substituted with the instructed value.

Bryce
  • 1
  • 1
  • For a fixed number of variables, I would try nesting some For...next loops. One level of nested loop for each variable. – elliot svensson Aug 03 '18 at 15:40
  • For variable number of variables, maybe it's time to try a recursive function? I suppose the recursive function would loop through everything that already exists and provide a new list for every pre-existing node. – elliot svensson Aug 03 '18 at 15:40
  • Possible duplicate of [Permutations in VBA Excel](https://stackoverflow.com/questions/29078363/permutations-in-vba-excel) – Comintern Aug 03 '18 at 15:43
  • I don't find this very clear. What is the specific form of the input and desired output? E.g. are the input and output both 2-column VBA arrays? Two column Ranges? Something else? Pretend that we don't already know what you are trying to do. – John Coleman Aug 03 '18 at 15:44
  • Actually, maybe there's no reason to dive into the rabbit hole on recursion... just make an array with a string representing the combination (assuming that's possible). For each variable, loop through the entire list and add new strings representing the new combinations. This only works if you're not asking about order (only the combinations: 12 = 21). – elliot svensson Aug 03 '18 at 15:44
  • 2
    Could you add the expected output if the original input also included `C_min, C_max`? – Joseph Wood Aug 03 '18 at 19:26

1 Answers1

0

Here is a VBA function which can be used to solve one interpretation of your problem:

Function Products(A As Variant) As Variant
    'A is assumed to be a 2-column 1-based array
    'The function returns another 2-column 1-based array
    'Where each successive 4 rows gives the Cartesian product
    'of two of the rows of A, with the earlier row
    'providing the first element and the latter row the second

    Dim i As Long, j As Long, k As Long, n As Long
    Dim P As Variant

    n = UBound(A, 1)
    ReDim P(1 To 2 * n * (n - 1), 1 To 2)

    k = 1
    For i = 1 To n - 1
        For j = i + 1 To n
            P(k, 1) = A(i, 1)
            P(k, 2) = A(j, 1)
            P(k + 1, 1) = A(i, 1)
            P(k + 1, 2) = A(j, 2)
            P(k + 2, 1) = A(i, 2)
            P(k + 2, 2) = A(j, 1)
            P(k + 3, 1) = A(i, 2)
            P(k + 3, 2) = A(j, 2)
            k = k + 4
        Next j
    Next i
    Products = P
End Function

Used like: Range("C1:D12").Value = Products(Range("A1:B3").Value)

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • Hey John, sorry that my question wasn't very clear I was afraid too much information would over complicate it. The input will always be an array with two columns, and with each row representing a variable. The output will have as many columns as there were variables, and each row represents a possible combination. I will add edit to my post to explain what this is being used for. – Bryce Aug 03 '18 at 17:47