Results 1 to 7 of 7

Group numbers in two columns using collections

Threaded View

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Group numbers in two columns using collections

    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?
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] is there any book to teach System.Collections.ArrayList, System.Collections.SortedList,
    By Flora20 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-01-2017, 06:35 AM
  2. [SOLVED] Macro to generate groups randomly of 20 numbers, being 10 of group A and 10 of group B,
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 11-14-2017, 08:02 AM
  3. [SOLVED] Taking single input as numbers and storing in arrays or collections
    By come2me in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2017, 05:02 AM
  4. Replies: 1
    Last Post: 02-03-2016, 06:43 PM
  5. Collections within class module collections
    By AndyLitch in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2013, 06:00 AM
  6. Replies: 2
    Last Post: 01-18-2013, 12:45 PM
  7. [SOLVED] How can I convert a group of numbers to a group of letters?
    By CarlG in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-18-2006, 10:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1