asked on

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

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

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 Hermle

ASKER

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

Roy Cox

Pleased to help