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.
Create a grouping of numbers
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Create a grouping of numbers
You do not have the required permissions to view the files attached to this post.
-
- 2StarLounger
- Posts: 170
- Joined: 26 Jan 2017, 07:24
Re: Create a grouping of numbers
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),""))
=UNIQUE(LEFT(A2:A17,1))
=TEXTJOIN(",",,IF(LEFT($A$2:$A$18,1)=D2,RIGHT($A$2:$A$18,1),""))
You do not have the required permissions to view the files attached to this post.
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Create a grouping of numbers
Thanks, but I don't' have 365. I have 2016.
-
- PlatinumLounger
- Posts: 4930
- Joined: 31 Aug 2016, 09:02
Re: Create a grouping of numbers
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
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Create a grouping of numbers
Thank you. This works great.