Generating pairs from a given set
 

I have a column with 15 values and from these values I need a list of all
non-repeating pairs possible. Simple task, but for us not-so-well-versed in
the Excel development environment - is there a code sample? Thanks!

Generating pairs from a given set
 

Hi,

This assumes your 15 numbers are in the range A1 to A15. Right click the
sheet tab, view code and paste this in and run it:-

Sub permem()
Dim count, perms As Long
count = 1
Dim myRange As Range
Set myRange = Range("A1:A15")
numbers = 15
Dim n(15)
For p = 1 To numbers
n(p) = Cells(p, 1).Value
Next
For i = 1 To numbers
For j = 1 To numbers
If j <= i Then GoTo 100
For x = 1 To numbers
If i = x Then firstno = n(x)
Next
For x = 1 To numbers
If j = x Then secondno = n(x)
Next
Cells(count, 3).Value = firstno & ", " & secondno
count = count + 1
100 Next
Next

End Sub


Mike

"Mac" wrote:

I have a column with 15 values and from these values I need a list of all
non-repeating pairs possible. Simple task, but for us not-so-well-versed in
the Excel development environment - is there a code sample? Thanks!

Generating pairs from a given set
 

Mike, you're the expert I needed.:-) Now there's another step I'd like to
perform on this data;
Assume I have applied your code (this time on 14 values in a column), so now
I have 91 value pairs. Next, from these 91 pairs, I need to generate all
possible sets, where each set conforms to the following criteria:
1.the set (that is - the 7 pairs in the set) contains all numbers from the
original 14
2. no number is repeated throughout the set
Imagine a tennis tournament; you have 14 players and you want all of them to
play in each round (7 matches per round) until each player has played all the
others.
Again - this a simple combinatorics task but for an Excel expert like you
only. Can you help once more?:-)

"Mike H" wrote:



Hi,

This assumes your 15 numbers are in the range A1 to A15. Right click the
sheet tab, view code and paste this in and run it:-

Sub permem()
Dim count, perms As Long
count = 1
Dim myRange As Range
Set myRange = Range("A1:A15")
numbers = 15
Dim n(15)
For p = 1 To numbers
n(p) = Cells(p, 1).Value
Next
For i = 1 To numbers
For j = 1 To numbers
If j <= i Then GoTo 100
For x = 1 To numbers
If i = x Then firstno = n(x)
Next
For x = 1 To numbers
If j = x Then secondno = n(x)
Next
Cells(count, 3).Value = firstno & ", " & secondno
count = count + 1
100 Next
Next

End Sub


Mike

"Mac" wrote:

I have a column with 15 values and from these values I need a list of all
non-repeating pairs possible. Simple task, but for us not-so-well-versed in
the Excel development environment - is there a code sample? Thanks!