Aggregate numbers in Column A in one cell, separated by commas

Dear Experts,

I got hundreds of numbers in Column A (starting in A2) which I would like to aggregate (by means of a macro) in one cell in B2, separated by commas.

Example

Column A Column B
Column Header
90-234-55-77 90-234-55-77,90-445-22-99,90-343-11-45,90-343-12-99,90-574-13-21,90-537-19-43,etc.
90-445-22-99
90-343-11-45
90-343-12-99
90-574-13-21
90-537-19-43
etc.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Andreas HermleTeam leaderAsked: 2017-03-11

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license! Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 28

Roy CoxGroup Finance ManagerCommented: 2017-03-11

Try this

Option Explicit

Sub x()
    Dim rCl As Range
    Dim iX As Integer
    With ActiveSheet
        .Cells(1, 2).ClearContents
        For Each rCl In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
            .Cells(1, 2).Value = .Cells(1, 2).Value & "," & rCl.Value
        Next rCl
        iX = Len(.Cells(1, 2).Value) - 1
        .Cells(1, 2).Value = Right(.Cells(1, 2).Value, iX - 1)
    End With

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 31

ShumsExcel & VBA ExpertCommented: 2017-03-11

Hi Andreas,

Please try below:

Sub CombineRange()
Dim Ws As Worksheet
Dim LastR As Long
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
Set Ws = ActiveSheet
LastR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Set InputRng = Ws.Range("A2:A" & LastR)
Set OutRng = Ws.Range("B2")
Application.ScreenUpdating = False
OutStr = ""
For Each Rng In InputRng
    If OutStr = "" Then
        OutStr = Rng.Value
    Else
        OutStr = OutStr & ", " & Rng.Value
    End If
Next
OutRng.Value = OutStr
Application.ScreenUpdating = True
End Sub

Open in new window

LVL 28

Roy CoxGroup Finance ManagerCommented: 2017-03-11

Just a slight amendment

Option Explicit

Sub x()
    Dim rCl As Range
    Dim iX As Integer
    With ActiveSheet
        .Cells(1, 2).ClearContents
        For Each rCl In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
            .Cells(1, 2).Value = .Cells(1, 2).Value & "," & Trim(rCl.Value)
        Next rCl
        iX = Len(.Cells(1, 2).Value)
        .Cells(1, 2).Value = Right(.Cells(1, 2).Value, iX - 1)
    End With

End Sub

Open in new window

Andreas HermleTeam leaderAuthor Commented: 2017-03-11

Dear both,

both solutions work just fine, a superb job from both of you. I really appreciate it. Since Roy was the first to answer the majority of the points go to his account.

Again, thank you very much and have a nice Sunday.

Regards, Andreas

LVL 28

Roy CoxGroup Finance ManagerCommented: 2017-03-11

Pleased to help