Looping through permutations

[marston.gould]

I have data in a series of columns A, B, C, D, E, F
Each column has the same amount of rows in it.

Can someone think of an elegant way to loop through the creation of a range that creates the following permutations

XRng = range("A1:A100","B1:B100","C1:C100","D1100","E1:E100","F1:F100")
XRng = range("A1:A100","B1:B100","C1:C100","D1100","E1:E100")
XRng = range("A1:A100","B1:B100","C1:C100","D1100","F1:F100")
XRng = range("A1:A100","B1:B100","C1:C100","E1:E100","F1:F100")
XRng = range("A1:A100","B1:B100","D1100","E1:E100","F1:F100")
XRng = range("A1:A100","C1:C100","D1100","E1:E100","F1:F100")
XRng = range("B1:B100","C1:C100","D1100","E1:E100","F1:F100")
XRng = range("A1:A100","B1:B100","C1:C100","D1100")
XRng = range("A1:A100","B1:B100","C1:C100","E1:E100")
XRng = range("A1:A100","B1:B100","C1:C100","F1:F100")
XRng = range("A1:A100","B1:B100","D1100","E1:E100")
XRng = range("A1:A100","B1:B100","D1100","F1:F100")
XRng = range("A1:A100","B1:B100","E1:E100","F1:F100")
XRng = range("A1:A100","C1:C100","D1100","E1:E100")
XRng = range("A1:A100","C1:C100","D1100","F1:F100")
XRng = range("A1:A100","C1:C100","E1:E100","F1:F100")
XRng = range("B1:B100","C1:C100","D1100","E1:E100")
XRng = range("B1:B100","C1:C100","D1100","F1:F100")
XRng = range("B1:B100","C1:C100","E1:E100","F1:F100")
XRng = range("B1:B100","D1100","E1:E100","F1:F100")
XRng = range("C1:C100","D1100","E1:E100","F1:F100")
XRng = range("A1:A100","B1:B100","C1:C100")
XRng = range("A1:A100","B1:B100","D1100")
XRng = range("A1:A100","B1:B100","E1:E100")
XRng = range("A1:A100","B1:B100","F1:F100")
XRng = range("A1:A100","C1:C100","D1100")
XRng = range("A1:A100","C1:C100","E1:E100")
XRng = range("A1:A100","C1:C100","F1:F100")
XRng = range("A1:A100","D1100","E1:E100")
XRng = range("A1:A100","D1100","F1:F100")
XRng = range("A1:A100","E1:E100","F1:F100")
XRng = range("B1:B100","C1:C100","D1100")
XRng = range("B1:B100","D1:E100","E1:E100")
XRng = range("B1:B100","D1100","F1:F100")
XRng = range("B1:B100","E1:E100","F1:F100")
XRng = range("C1:C100","D1100","E1:E100")
XRng = range("C1:C100","D1100","F1:F100")
XRng = range("C1:C100","E1:E100","F1:F100")
XRng = range("D1100","E1:E100","F1:F100")
XRng = range("A1:A100","B1:B100")
XRng = range("A1:A100","C1:C100")
XRng = range("A1:A100","D1100")
XRng = range("A1:A100","E1:E100")
XRng = range("A1:A100","F1:F100")
XRng = range("B1:B100","C1:C100")
XRng = range("B1:B100","D1100")
XRng = range("B1:B100","E1:E100")
XRng = range("B1:B100","F1:F100")
XRng = range("C1:C100","D1100")
XRng = range("C1:C100","E1:E100")
XRng = range("C1:C100","F1:F100")
XRng = range("D1100","E1:E100")
XRng = range("D1100","F1:F100")
XRng = range("E1:E100","F1:F100")
XRng = range("A1:A100")
XRng = range("B1:B100")
XRng = range("C1:C100")
XRng = range("D1100")
XRng = range("E1:E100")
XRng = range("F1:F100")

Is there a way to create these non-re-occurring permutations

 

[joeu2004]

Can someone think of an elegant way to loop through the
creation of a range that creates the following permutations

First, it appears that you are interested in combinations, not permuations.
COMBIN(6,k), for k=6,5,4,3,2,1, tells you how many combinations to expect in
each group. You will see that you are missing some combinations.

Second, your Range syntax is invalid. I presume you mean
Range("A1:A100,B1:B100,C1:C100,D1100,E1:E100,F1:F100"), for example.

Third, it is unclear whether you simply want some output. (I use
Debug.Print to write to the Immediate Window.) Or if you actually want to
set a Range variable xRng. (See the Set statement for that purpose.)

See the macro below.

The Debug.Print output is a little more than what you ask for. I presume
that you know how to customize it to your needs exactly. Press ctrl+G to
see the output in the Immediate Window. The form of the output is:

COMBIN(6,6) = 1
01: xRng = Range("A1:A100,B1:B100,C1:C100,D1100,E1:E100,F1:F100")
COMBIN(6,5) = 6
01: xRng = Range("A1:A100,B1:B100,C1:C100,D1100,E1:E100")
02: xRng = Range("A1:A100,B1:B100,C1:C100,D1100,F1:F100")
....etc...

The macro....

Option Base 1
Option Explicit

Sub combinKofN()
Dim rngs
Dim nRngs As Long, maxCombin As Long, nCombin As Long
Dim nSelect As Long, i As Long, j As Long
Dim r As String
Dim xRng As Range

rngs = Array("A1:A100", "B1:B100", "C1:C100", "D1100", "E1:E100",
"F1:F100")
nRngs = UBound(rngs)

For nSelect = nRngs To 1 Step -1
maxCombin = WorksheetFunction.Combin(nRngs, nSelect)
Debug.Print "COMBIN(" & nRngs & "," & nSelect & ") = " & maxCombin

ReDim idx(1 To nSelect) As Long
For i = 1 To nSelect: idx(i) = i: Next

nCombin = 0
Do
' generate next combination
nCombin = nCombin + 1
r = rngs(idx(1))
For i = 2 To nSelect
r = r & "," & rngs(idx(i))
Next
Set xRng = Range(r)
' ...perhaps you want to use xRng for some purpose here...
Debug.Print Format(nCombin, "00") & _
": xRng = Range(""" & xRng.Address(False, False) & """)"
If nCombin = maxCombin Then Exit Do

' next combination index
i = nSelect: j = 0
While idx(i) = nRngs - j
i = i - 1: j = j + 1
Wend
idx(i) = idx(i) + 1
For j = i + 1 To nSelect
idx(j) = idx(j - 1) + 1
Next
Loop
Next

End Sub

 

[joeu2004]

PS....

rngs = Array("A1:A100", "B1:B100", "C1:C100", "D1100", "E1:E100",
"F1:F100")

It appears that this line got split during the submission process. It
should be written:


rngs = Array("A1:A100", "B1:B100", "C1:C100", "D1100", _
"E1:E100", "F1:F100")

Set xRng = Range(r) [....]
Debug.Print Format(nCombin, "00") & _
": xRng = Range(""" & xRng.Address(False, False) & """)"

Of course, I could have written simply:

Debug.Print Format(nCombin, "00") & _
": xRng = Range(""" & r & """)"

I use xRng.Address(False,False) as a way to confirm that xRng got set to the
intended Range collection.

 

[marston.gould]

This was very helpful......
After putting the variables into watch mode, I think that I get a better sense of how/why this works the way it does.

I'm sorry I didn't better communicate that the main purpose of this was to utilize the Xrng/Yrng in a linest function.

The immediate goal I'm trying to achieve is to calculate the coefficients, T-stats, and R-squared for each of the combinations.

So I modified - slightly the code you provided to try to achieve the first part of this goal - get the regression and then spit out the coefficient/T-stat of first variable. Eventually, I have to do all variable coefficients/T-stats.

Now the problem I'm facing is that there is some kind of mismatch - on the linest with the xrng. I know this because when I put in a fixed range into the code - it works fine.

Thoughts?

Appreciations in advance!

Marston

 

[marston.gould]

This was very helpful......
After putting the variables into watch mode, I think that I get a better sense of how/why this works the way it does.

I'm sorry I didn't better communicate that the main purpose of this was to utilize the Xrng/Yrng in a linest function.

The immediate goal I'm trying to achieve is to calculate the coefficients, T-stats, and R-squared for each of the combinations.

So I modified - slightly the code you provided to try to achieve the first part of this goal - get the regression and then spit out the coefficient/T-stat of first variable. Eventually, I have to do all variable coefficients/T-stats.

Now the problem I'm facing is that there is some kind of mismatch - on the linest with the xrng. I know this because when I put in a fixed range into the code - it works fine.

Thoughts?

Appreciations in advance!

Marston

 

[joeu2004]

This was very helpful......
After putting the variables into watch mode, I think
that I get a better sense of how/why this works the
way it does.

My apologies for not explaining the algorithm. I thought it might be a
homework assignment, and some things should be left for the student to
figure out ;-).

The key trick is how we choose the "next combination index". To understand
that code, it might be helpful to review how we generate all combinations
efficiently.

In general, if we want to generate all combinations selecting k objects from
n objects, we might write the following k nested for-loops:

for idx(1) = 1 to n-(k-1)
for idx(2) = idx(1)+1 to n-(k-2)
[....]
for idx(k-1) = idx(k-2)+1 to n-1
for idx(k) = idx(k-1)+1 to n
combo = array(myData(idx(1)),myData(idx(2)),...,myData(idx(k)))
next idx(k),...,idx(1)

The "next combination index" implements that logic effectively for a
variable number of for-loops.

Now the problem I'm facing is that there is
some kind of mismatch - on the linest with
the xrng.

I will comment on that in the thread that you posted separately titled
"Runtime error on linest".

 

[marston.gould]

I apologize if you already received this - for some reason, some of the posts I'm making do not appear in the group - even after waiting several hours.

So this was very helpful. I should have mentioned up front that I was going to use the range for a specific purpose vs. needing to print it.

Essentially, what I'm trying to do is to use each combination xRng as a candidate for a regression calculation.

yRng is a predefined column of data of equal length to the xRng

Now the problem I'm having is that when I try to blend this code with the code I had for calculating the LinEst, I get a runtime error 1004.

After Set xRng = Range(r) I have inserted the following:

v = Application.WorksheetFunction.LinEst(yRng,xRng,0,True)
However, when I use xRng from this code, I get the error. When I instead use a fixed defined xRng = Range("A1:F100") it runs.

Any thoughts?

 

[joeu2004]

I apologize if you already received this - for some
reason, some of the posts I'm making do not appear
in the group - even after waiting several hours.

Yes, it appears that Google Groups is having its problems again. None of
your follow-up postings to this thread appear in GG, nor do any your
"duplicate" postings about the same problem in m.p.e.programming.

It is such problems that cause me recently to abandon GG. Instead, I use
the newserver news.eternal-september.org, which you can register to use for
free at http://eternal-september.org. Then I set up Outlook Express as a
newsreader on my computer.

news.eternal-september.org has been very reliable for the short time that I
have been using it, about one month.

(Of course, you will not see this until the GG problem is cleared up.)