CODEWITHSUNDEEP
vbaexcel
Rick Arko
Rick Arko

Reputation: 680

How to create every pairwise combination in Excel vba

I have an exhaustive list of 2-digit zip codes in columns 1 and 2 (01 to 99). I want to create a third column that will contain every pairwise combination of two-digit zipcodes. So that my data will look something like this where column 3 contains every pairwise combination of columns 1 and 2 (9801 rows). 

col1 col2  col3
01    01   01 to 01
02    02   02 to 02
03    03   03 to 03
04    04   04 to 04
05    05   05 to 05
06    06   06 to 06
.     .       .
.     .       .
99    99  99 to 99

Is there a way to code this into a simple vba script?

Upvotes: 1

Views: 880

Answers (2)

Alexander Bell
Alexander Bell

Reputation: 7918

Pertinent to your task, you can use the following Excel VBA code snippet:

Sub ColumnConcatenation()
    Dim i As Long, j As Long
    Const max = 99
    Application.ScreenUpdating = False
    For i = 1 To max
        For j = 1 To max
            Range("C" & (i - 1) * max + j) = Range("A" & i) & " to " & Range("B" & j)
        Next j
    Next i
    Application.ScreenUpdating = True
End Sub

Hope this may help.

Upvotes: 1

Rick Arko
Rick Arko

Reputation: 680

I have almost no VBA experience, but this was easier to figure out than I originally thought.

Sub pairwise()

Dim x As Integer
Dim x2 As Integer
Dim x3 As Double

x3 = 1

For x = 1 To 99
    For x2 = 1 To 99

    Cells(x3, 5) = Cells(x, 1)
    Cells(x3, 6) = Cells(x2, 2)

    x3 = x3 + 1

    Next
Next
End Sub

This creates 2 columns which I can then Concatenate by pasting the formula =E1&" to "&F1 in G1:G9801. If there's a more elegant way of doing this I'd gladly accept that as an answer.

Upvotes: 0

Related Questions