median of combinations from values in a column

[Andreas]

Hi,

I posted this already here:

http://groups.google.com/group/micr...58d?lnk=gst&[email protected]#dbb2357c7d62558d

and received some good solutions. However, my original problem has
become a bit more complicated.

Here's the original problem:

I'm trying to figure out how to generate all combinations from a list
of N values in a column, let's say column A.

In particular, I want to have all combinations of 2 values, 3 values,
4, ... up to 7 values.

To give an example: Let's assume I have a list of only 3 values
(1,2,3) for which I want to have all combinations of two values. In
this case, the result would be 1,2; 1,3; and 2;3. The ordering of the
values does not matter, i.e. duplicates should be eliminated.

Now, the extension is that I need the median of each combination
rather than the combinations themselves. So it would be nice if the
code would already calculate the median.

Besides the solution in the link, I also found another code that works
well - but still does not provide me with the median (see below).

An additional problem is the limitation in the number of rows in
excel. Thus, it would be great to have the output in a txt file or
something similar, which may ideally be imported to SPSS.

Any suggestions? Thanks in advance,
Andreas

Sub test()
ListCombos Range("A1:A5"), 3, 7
End Sub

Sub ListCombos(r As Range, m As Long, iRow As Long)
' lists the combinations of r choose m starting in row iRow
Dim ai() As Long
Dim i As Long
Dim n As Long
Dim vOut As Variant

n = r.Rows.Count

Redim ai(1 To m)
Redim vOut(1 To m)

ai(1) = 0
For i = 2 To m
ai(i) = i
Next i

Do
For i = 1 To m - 1
If ai(i) + 1 < ai(i + 1) Then
ai(i) = ai(i) + 1
Exit For
Else
ai(i) = i
End If
Next i
If i = m Then
If ai(m) < n Then
ai(m) = ai(m) + 1
Else
Exit Sub
End If
End If

' put the values in the variant
For i = 1 To m
vOut(i) = r(ai(i))
Next i
' list it
Cells(iRow, 1).Resize(, m).Value = vOut
iRow = iRow + 1
Loop
End Sub

 

[Barb Reinhardt]

Replace

Cells(iRow, 1).Resize(, m).Value = vOut
iRow = iRow + 1

with
Cells(iRow, 1).Resize(, m).Value = vOut
Cells(iRow, 1).Offset(0, m).Value =
WorksheetFunction.Median(Cells(iRow, 1).Resize(, m))
iRow = iRow + 1

near the end of your code. The middle line is an addition, everything else
remains the same.

 

[graefe.andreas]

Hi Barb,

Thanks for your answer.

I did this but I can't see that anything has changed. I still get the
combinations (each in one row) but no median.

In addition, how can I change the code to get the data in a txt file
without row limitations?

Andreas

 

[graefe.andreas]

Thanks Barb, this works perfect.

Now, one more thing. How can I get the data in a txt file so that I
can deal with the row limitations of Excel? I have problems with way
more than 65,000 rows. I want to import the data in SPSS later.

Andreas

 

[graefe.andreas]

In addition, is it possible to show *only* the median? This would make
it easier in terms of importing to SPSS later.

Andreas

 

[Barb Reinhardt]

I've written to a text file once and truthfully don't remember how I did it.
I'd do a google search for VBA write to text file. I bet you'll find what
you need.

 

[Dana DeLouis]

Just curious. Can SPSS read in that many records at once?

=COMBIN(66,7) = 778,789,440

Here is just an idea. I don't know if it is of any use though.
If you are generating "Subsets" of size 4 in lexicographical order (ie
sorted) and use 4 variables a,b,c,&d,
then the mean is (a+b+c+d)/4
and the median is (b+c)/2

You execute division 1,557,578,880 times, and store real numbers in your
file.
Another idea might be to store integer values. ie just store (a+b+c+d) and
(b+c) in the text file, and see if SPSS can divide your final answers by 4
and 2.

This is not quite the code I use for Subsets, but here is a general idea for
outputting to a text file.
This is a simple idea that I'm sure you could modify.
Given a set 1-6, this generates "Subsets" of size 4.
However, this does "NOT" divide the sums by 4 and 2.
The "Mean" and "Median" go to different files.

Sub Demo()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long

Dim n As Long
Dim z As Long

Dim FileMean
Dim FileMedian

n = 6
z = n + 1

ChDir "C:\Temp"
FileMean = FreeFile
Open "Mean" For Output As #FileMean

FileMedian = FreeFile
Open "Median" For Output As #FileMedian

For a = 0 + 1 To n - 3
For b = a + 1 To n - 2
For c = b + 1 To n - 1
For d = c + 1 To n

Write #FileMean, a + b + c + d '(/4 if you wish)
Write #FileMedian, b + c '(/2 if you wish)

Next d, c, b, a
Close
End Sub