Using Excel for statistical analysis - a question for problem-solvers
This is a simple way of doing it:
Sub test()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim e As Long
Dim f As Long
Dim n As Long
Dim UBV As Long
Dim arrVars
Dim arrCombin
arrVars = Array("1", "2", "3", "4", "5", "6", "7")
UBV = UBound(arrVars)
ReDim arrCombin(0 To (2 ^ (UBV + 1)) - 2)
MsgBox (2 ^ (UBV + 1)) - 1, , _
"possible combinations"
'1 item
For a = 0 To UBV
arrCombin(n) = arrVars(a)
n = n + 1
Next a
'2 items
For a = 0 To UBV - 1
For b = a + 1 To UBV
arrCombin(n) = arrVars(a) & _
arrVars(b)
n = n + 1
Next b
Next a
'3 items
For a = 0 To UBV - 2
For b = a + 1 To UBV - 1
For c = b + 1 To UBV
arrCombin(n) = arrVars(a) & _
arrVars(b) & _
arrVars(c)
n = n + 1
Next c
Next b
Next a
'4 items
For a = 0 To UBV - 3
For b = a + 1 To UBV - 2
For c = b + 1 To UBV - 1
For d = c + 1 To UBV
arrCombin(n) = arrVars(a) & _
arrVars(b) & _
arrVars(c) & _
arrVars(d)
n = n + 1
Next d
Next c
Next b
Next a
'5 items
For a = 0 To UBV - 4
For b = a + 1 To UBV - 3
For c = b + 1 To UBV - 2
For d = c + 1 To UBV - 1
For e = d + 1 To UBV
arrCombin(n) = arrVars(a) & _
arrVars(b) & _
arrVars(c) & _
arrVars(d) & _
arrVars(e)
n = n + 1
Next e
Next d
Next c
Next b
Next a
'6 items
For a = 0 To UBV - 5
For b = a + 1 To UBV - 4
For c = b + 1 To UBV - 3
For d = c + 1 To UBV - 2
For e = d + 1 To UBV - 1
For f = e + 1 To UBV
arrCombin(n) = arrVars(a) & _
arrVars(b) & _
arrVars(c) & _
arrVars(d) & _
arrVars(e) & _
arrVars(f)
n = n + 1
Next f
Next e
Next d
Next c
Next b
Next a
'all items, only one combination possible
'(as order doesn't alter)
For a = 0 To UBV
If a = 0 Then
arrCombin(n) = arrVars(a)
Else
arrCombin(n) = arrCombin(n) & arrVars(a)
End If
Next a
Columns(1).Clear
For n = 0 To UBound(arrCombin)
Cells(n + 1, 1) = arrCombin(n)
Next n
End Sub
Just extend to 20 and alter the concatenation to adding variables.
I ams sure there is a much more elegant way to do this (maybe a recursive
Sub), but
this will do.
RBS
wrote in message
...
Thanks -- much appreciated!
Any ideas on how I would go about creating a script that would
calculate the totals of all 1048575 variables in an Excel
spreadsheet? I have a worksheet with two simple columns - one is
called variables, the other is called value. I am trying to figure
out how to manipulate that data.
Thanks!!
SB
|