Microsoft Excel - Creating Combinations - Asked By Cherifa Hima on 11-Apr-13 03:24 PM

Hi,
I have two columns: Orig and Dest and I need to combine them in many pairs. I need a vba code that will help me automate that.


Orig  Dest
A     X
B     Y
C    Z  
E    V
F    WW

The results should two columns like this:
Orig  Dest
A     X
A     Y
A     Z
A     V
A   W
B    X
B    Y
B     Z
B   V
B   WW
 and so on

Thanks.
Harry Boughen replied to Cherifa Hima on 13-Apr-13 03:35 AM
Hello Cherifa,
Try:
Option Explicit

Sub Combinations()
Dim rngOne, rngTwo, rngCell1, rngCell2 As Range
Dim intCount As Integer

Set rngOne = Range("A2:A6")
Set rngTwo = Range("B2:B6")
intCount = 0
For Each rngCell1 In rngOne
For Each rngCell2 In rngTwo
Range("C2").Offset(intCount, 0).Value = rngCell1.Value
Range("C2").Offset(intCount, 1).Value = rngCell2.Value
intCount = intCount + 1
Next rngCell2
Next rngCell1

End Sub
Cherifa Hima replied to Harry Boughen on 15-Apr-13 10:58 AM
Thanks.
What if I have more than 5 observations by column. How can I modify the code to include any number of orig and dest?
Harry Boughen replied to Cherifa Hima on 15-Apr-13 05:11 PM
Hello Cherifa,
Try
Sub Combinations()
Dim rngOne, rngTwo, rngCell1, rngCell2 As Range
Dim intCount, intRows As Integer

Set rngOne = Range("A2").CurrentRegion
intRows = rngOne.Rows.Count
If intRows > 1 Then
    Set rngOne = rngOne.Offset(1, 0).Resize(intRows - 1, 1)
Else
    Exit Sub
End If

Set rngTwo = rngOne.Offset(0, 1)
intCount = 0
For Each rngCell1 In rngOne
For Each rngCell2 In rngTwo
Range("C2").Offset(intCount, 0).Value = rngCell1.Value
Range("C2").Offset(intCount, 1).Value = rngCell2.Value
intCount = intCount + 1
Next rngCell2
Next rngCell1

End Sub

Regards
Harry
Cherifa Hima replied to Harry Boughen on 18-Apr-13 03:08 PM
Thanks Harry. You are the best:)