I would like to know if there is an excel function or how I could write an
excel macro that will enumerate the unique 2-subsets of an n-set.
Thanks very much,
Tim Dawes
Must haves:
Concretely, what I want is this.
I will have an excel spreadsheet. One column, say column A, will have
information in cells, usually n will be between 10 and 30.
I want a macro to copy the information from the cells in column A into
columns B and C, 2 items per row.
So I want 2-subsets on a 20-set (for instance) and I want only the
unique subsets (13, 14) is the same as (14, 13).
I would like instances of a given data item to appear as infrequently as
possible.
In other words if I have a 10-set, I don’t want to start
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
where 1 is repeated in each of the first X partitions.
I would rather have
1 2
3 4
5 6
7 8
9 10
2 3
4 5
6 7
8 9
10 1
So that the first instance of 1 is separated from the second instance by 8
partitions and
the first instance of 2 is separated from the second by 5 partitions. As
much space between instances as possible.
Answer Tim,
Sorry for late reply (I had couple of break fixes in my current SAP application), here you go:
Dim x As Integer
Dim y As Integer
x = 10 'Customize
y = 1
z = 1
While z <= 2 'I did iterate only twice, you can increase this number as many as you want
While x <= 30 'Customize
Range("B" & Trim(Str(y))).Select
ActiveCell.FormulaR1C1 = x
x = x + 1
If x > 30 Then x = 10
Range("C" & Trim(Str(y))).Select
ActiveCell.FormulaR1C1 = x
x = x + 1
y = y + 1
Wend
z = z + 1
Wend
End Sub
