We help IT Professionals succeed at work.

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

88 Views
Last Modified: 2017-03-11
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
Comment
Watch Question

Group Finance Manager
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
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 leader

Author

Commented:
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 CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Pleased to help