Create a grouping of numbers

[gailb]

In column A I have a group of numbers
66
67
67
68
68
68
68
69
69
69
69
70
70
71
71
72
73
75

Would it be possible to create the following result. The first column has the left one digit and the second column will have remainder separated by a comma.

Capture.1JPG.JPG

[Nabeel]

if you have 365 then it would be more easy

=UNIQUE(LEFT(A2:A17,1))
=TEXTJOIN(",",,IF(LEFT($A$2:$A$18,1)=D2,RIGHT($A$2:$A$18,1),""))

Capture.PNG

[gailb]

Thanks, but I don't' have 365. I have 2016.

[YasserKhalil]

Try this code

Code: Select all

Sub Test()
    Dim a, b, x, f As Boolean, i As Long, n As Long
    a = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    ReDim b(1 To UBound(a), 1 To 2)
    For i = LBound(a) To UBound(a)
        If f = False Then x = Val(Left(a(i, 1), 1)): n = n + 1: f = True
        If f Then
            b(n, 1) = Val(Left(a(i, 1), 1))
            Do
                b(n, 2) = b(n, 2) & IIf(b(n, 2) = Empty, Empty, ", ") & Right(a(i, 1), 1)
                If UBound(a) = i Then Exit For
                i = i + 1
                If Val(Left(a(i, 1), 1)) <> x Then f = False
            Loop Until f = False
            i = i - 1
        End If
    Next i
    Range("C1").Resize(n, UBound(b, 2)).Value = b
End Sub

[gailb]

Thank you. This works great.