Expert: Subbu - 9/15/2009

Question
I hope you’ll consider taking this on.

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

Let me know if you have any questions.

Subbu.