Excel VBA concatenate Column 1 for range of values in Column 2

I have a set of rows & column as below

Column1    Column2
123        Value1
456        Value1
789        Value2
101        Value2
234        Value2
567        Value3
890        Value4

I would like to concatenate column1 based on column2 range like:

Column3
123
123,456
789
789,101
789,101,234
567
890

I tried this using Formula and did it, but is 开发者_如何转开发there a better way (like in Macro) to do this?

=IF(B2=B1,C1&","&C2,C2)

and pick the last row for each value

---

Well, this macro will do it. I wouldn't necessarily say it was better, though!

Sub Macro1()
    Dim Source As Range
    Dim Control As Range
    Dim Output As Range
    Set Source = Range("A1")
    Set Control = Range("B1")
    Set Output = Range("C1")
    Dim StoreHere As String
    Dim Row As Integer
    Dim AddItOn As Boolean

    Row = 1
    StoreHere = ""

    While (Not (IsEmpty(Source.Cells(Row, 1))))
        If (Row > 1) Then
            If (Control.Cells(Row, 1) = Control.Cells(Row - 1, 1)) Then
                AddItOn = True
            Else
                AddItOn = False
            End If
        Else
            AddItOn = False
        End If

        If (AddItOn = True) Then
            StoreHere = StoreHere & "," & Source.Cells(Row, 1)
        Else
            StoreHere = Source.Cells(Row, 1)
        End If

        Output.Cells(Row, 1).NumberFormat = "@"
        Output.Cells(Row, 1) = StoreHere
        Row = Row + 1
    Wend
End Sub

---

Here's a smaller option

Sub Macro1()
    Dim cl As Range

    Set cl = [A1]  ' set to where your data starts 
    Do While cl <> ""
        If cl.Cells(1, 2) = cl.Cells(0, 2) Then
            cl.Cells(1, 3) = cl.Cells(0, 3) & "," & cl
        Else
            cl.Cells(1, 3) = CStr(cl)
        End If
        Set cl = cl.Cells(2, 1)
    Loop
End Sub

---

I'm trying to concatenate two ranges. For each cell in Range1 concatenate all values in Range2 (separated by "-"):

Range1 values: 50703, 50702
Range2 values: 52797, 52848
Concatenate: 50703-52797, 50703-52848, 50702-52797, 50702-52848