Combinatorial Arrays in VBA

[pdevito3]

Hey everyone, I've got a brain twister here that I could get another set of eyes on.

Goal: I essentially want to put all of the combinations from a set of values in one array into a second array.

So I have a one array with my values:

valueArr(3)
oneArr(0) = A
oneArr(1) = B
oneArr(2) = C
oneArr(3) = D

I want to put all of the combinations of these value into a second array, let's say combArr(5,1) -- arrays start at 0, so 5 because there are 6 total combinations and 1 because I only care about a length of 2-- so:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]combArr Location[/TD]
[TD]Value[/TD]
[TD]valueArray Location[/TD]
[/TR]
[TR]
[TD]combArr(0,0)[/TD]
[TD]A[/TD]
[TD]valueArray(0)[/TD]
[/TR]
[TR]
[TD]combArr(0,1)[/TD]
[TD]B[/TD]
[TD]valueArray(1)[/TD]
[/TR]
[TR]
[TD]combArr(1,0)[/TD]
[TD]A[/TD]
[TD]valueArray(0)[/TD]
[/TR]
[TR]
[TD]combArr(1,1)[/TD]
[TD]C[/TD]
[TD]valueArray(2)[/TD]
[/TR]
[TR]
[TD]combArr(2,0)[/TD]
[TD]A[/TD]
[TD]valueArray(0)[/TD]
[/TR]
[TR]
[TD]combArr(2,1)[/TD]
[TD]D[/TD]
[TD]valueArray(3)[/TD]
[/TR]
[TR]
[TD]combArr(3,0)[/TD]
[TD]B[/TD]
[TD]valueArray(1)[/TD]
[/TR]
[TR]
[TD]combArr(3,1)[/TD]
[TD]C[/TD]
[TD]valueArray(2)[/TD]
[/TR]
[TR]
[TD]combArr(4,0)[/TD]
[TD]B[/TD]
[TD]valueArray(1)[/TD]
[/TR]
[TR]
[TD]combArr(4,1)[/TD]
[TD]D[/TD]
[TD]valueArray(3)[/TD]
[/TR]
[TR]
[TD]combArr(5,0)[/TD]
[TD]C[/TD]
[TD]valueArray(2)[/TD]
[/TR]
[TR]
[TD]combArr(5,1)[/TD]
[TD]D[/TD]
[TD]valueArray(3)[/TD]
[/TR]
</tbody>[/TABLE]


So essentially the first dimension of the combArr() is going 0x3, 1x2, 2x1 and the second dimension keeps going 1,2,3, but it after the first dimension number changes it starts one nuber higher [1,2,3 (1st dim change) 2,3 (1st dim change) 3].

Here's a crack at starting it, but I am hitting a wall to finish it. Any help is greatly appreciated.

Sub combos()

' I am setting up the value array in a form before this, so value array length is established already at this point
' in this case, lets stick with the above example vaule array length -- valArrLength --is 3 (0,1,2,3,4).

Dim totalCombinations
Dim fixedLength 'for the length of the combination
Dim combArr()

'valArrLength = 3 and was established on earlier form
fixedLength = 2 'just need 2-way combinations
totalCombinations = Application.WorksheetFunction.Combin(valArrLength+1, fixedLength) '6 in this case
Redim combArr(totalCombinations -1,fixedLength-1) '-1 because arrays start at 0

' the magical nested for and if loops goes here... I think it is going to be dynamic values that get a +1 after a loop to bump them up. Regardless, I'm having trouble with it. 


' also, I know I can make a min and max length and have that on the outside of the loop in place of fixed length to get a result for
' multiple lengths, but I wanted to simplify this as much as possible




End Sub

 

[pdevito3]

Just FYI, I found this and it is pretty fantastic. Major respect for the author Krisztina Szabó


https://sites.google.com/site/e90e50fx/home/combinatorics-using-excel-formulas-and-examples

 

[pdevito3]

Despite the site, code would be much more preferable, so... Bump :wink: