I have two columns representing a 1:many relationship. I need to reduce this down to a 1:1 relationship where the many in columnn B are concatenated by commas. Data below:
zipcode neighbors 10001 10010 10001 10011 10001 10016 10001 10018 10001 10119 10001 10199 10003 10012
Here is what I want the output to look like:
zipcode neighbors 10001 10010, 10011, 10012, 10016, 10018, 10019, 10199
There are 9000 records so I need to run a loop until end of record.
Now sure how to do this.
I figured it out, thanks everyone. Code share below:
Sub Concatenate()
Dim oldValue As String
Dim newValue As String
Dim result As String
Dim counter As Integer
oldValue = ""
newValue = ""
result = ""
counter = 1
For i = 2 To 9401
newValue = Worksheets("data").Cells(i, 1)
If (oldValue <> newValue) Then
Worksheets("result").Cells(counter, 1).NumberFormat = "@"
Worksheets("result").Cells(counter, 2).NumberFormat = "@"
Worksheets("result").Cells(counter, 1) = oldValue
Worksheets("result").Cells(counter, 2) = result
counter = counter + 1
result = ""
End If
If (result = "") Then
result = Worksheets("data").Cells(i, 2)
Else
result = result + "," + Worksheets("data").Cells(i, 2)
End If
oldValue = newValue
Next i
End Sub