Hello everyone
I have a working code that groups the numbers in column B and this is the code
Sub Test()
Dim z As New Collection
Dim a As Variant
Dim b As Variant
Dim v As Variant
Dim w As Variant
Dim str1 As String
Dim i As Long
Dim j As Long
Dim k As Long
a = Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
For i = 1 To UBound(a, 1)
str1 = a(i, 1)
On Error Resume Next
z.Add Key:=str1, Item:=Array(a(i, 1), New Collection)
On Error GoTo 0
z(str1)(1).Add a(i, 2)
Next i
ReDim a(1 To z.Count, 1 To 2)
i = 0
For Each v In z
i = i + 1
a(i, 1) = v(0)
j = 0
ReDim b(1 To v(1).Count + 1)
For Each w In v(1)
j = j + 1
b(j) = w
Next w
For j = 1 To UBound(b) - 2
For k = j + 1 To UBound(b) - 1
If b(k) < b(j) Then
w = b(j)
b(j) = b(k)
b(k) = w
End If
Next k
Next j
a(i, 2) = b(1)
j = 1
For k = 2 To UBound(b)
If b(k) <> b(k - 1) + 1 Then
If j = k - 1 Then
a(i, 2) = a(i, 2) & " | " & b(k)
Else
a(i, 2) = a(i, 2) & "-" & b(k - 1) & " | " & b(k)
End If
j = k
End If
Next k
a(i, 2) = Left$(a(i, 2), Len(a(i, 2)) - 3)
Next v
Application.ScreenUpdating = False
Range("F2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
Application.ScreenUpdating = True
End Sub
How can I group the column C too to have it grouped in column H?
Bookmarks