Permutation/Combination/Pairs order unimportant.
 

Hi,
I'm trying to take a list of authors and create pairs from them. The order in which they appear in a pair doesn't matter and I don't want duplicates of these pairs (I need them to generate coauthorship data). So, I have:

A
B
C
D

And want to generate:
AB
AC
AD
BC
BD
CD

This leaves out matches like "BA" since I already have "AB" and the order is unimportant to me. I've used the code below to create the full list of matches, but the output *does* include both "BA" and "AB".

Sub Permutations_2()
Dim rRng As Range
Dim lRow As Long, j As Long, k As Long

Set rRng = Range("A1", Range("A1").End(xlDown)) ' The set of values

For j = 1 To rRng.Count
For k = 1 To rRng.Count
If j < k Then
lRow = lRow + 1
Range("C" & lRow) = Range("a" & j)
Range("D" & lRow) = Range("a" & k)
End If
Next k
Next j
End Sub

Any ideas on how to eliminate the duplicates - either in the macro or in an additional step?

Many thanks,

Permutation/Combination/Pairs order unimportant.
 

"HL8" wrote:
Sub Permutations_2()
Dim rRng As Range
Dim lRow As Long, j As Long, k As Long
Set rRng = Range("A1", Range("A1").End(xlDown)) ' The set of values
For j = 1 To rRng.Count
For k = 1 To rRng.Count
If j < k Then
lRow = lRow + 1
Range("C" & lRow) = Range("a" & j)
Range("D" & lRow) = Range("a" & k)
End If
Next k
Next j
End Sub

Any ideas on how to eliminate the duplicates -
either in the macro or in an additional step?

For j = 1 to rRng.Count-1
For k = j + 1 to rRng.Count
lRow = lRow + 1
Range("C" & lRow) = Range("a" & j)
Range("D" & lRow) = Range("a" & k)
Next
Next

More efficient:

Dim v As Variant
For j = 1 to rRng.Count-1
v = Range("a" & j)
For k = j + 1 to rRng.Count
lRow = lRow + 1
Range("C" & lRow) = v
Range("D" & lRow) = Range("a" & k)
Next
Next

This worked perfectly. Thanks for the help!