Excel number Groups

[cotech_10]

I have 'n' number of rows or lines with number values in various cellls across 'n' number of columns that I would like to group into 'n' grouping sizes.

In the example spreadsheet I may have 6 columns and 13 rows of numbers of either 35 or 45 numbers in the various rows and columns, there is no fixed number of columns and rows.

For example if my total number of cells that contain numbers is 45 and my grouping size is 5 then I would like to end up with 9 sets or groups of numbers. Or if my grouping size is 3 then I would end up with 15 sets/groups of numbers.

In this example we are going to select 9 groups of 5 numbers. What I would like to achieve is to start from the first cell in column B2 and in a horizontal fashion and from left to right group the numbers in each cell according to the different number size groups.

The problem for me is when undertaking this analysis it must be done in a consecutive manner and when a blank column is encountered it will need to continue and go down to the first cell beginning in the next row and continue on the analsysis by incorporating the next value in the next row if necessary.

Back to my example it would work like this:

Starting from cell B2 the grouping would start like this:

Group 1: (10,20,30,35,36)
Group 2: (99,5,1,202,200)
Group 3: (45,3,6,99,2)
Group 4: (4,22,800,450,201)
etc............
Group 9: (340,38,302,112,203)

The output within each group would be in separate cells on indvidual rows.

I would like to add variances to this as mentioned above and
possibly group numbers in the following manner:

45 number pool size:
Grouping sizes : 5, 9, 3 or 15

35 number pool size:
Grouping sizes : 7, 5,

 

[cotech_10]

Hi Dante,

That does work, but I was looking to copy the results into another area of the same sheet.
Also would it be possible to enter a NAMED RANGE in the dialog box where you input the
data range ?


Thanking You

 

[DanteAmor]

Hi Dante,

That does work,
Also would it be possible to enter a NAMED RANGE in the dialog box where you input the
data range ?


Thanking You

Try this

Sub number_Groups()
    Dim c As Range, n As Variant, rango As Range, correct As Boolean
    Dim nk As Variant, q As Long, cad As String, col As Long
    Dim i As Long, j As Long, k As Long, nums As Variant
    
    'On Error Resume Next
    'With Application
    '    Set rango = .InputBox("select range", Title:="", Default:=Selection.Address, Type:=8)
    '    If rango Is Nothing Then Exit Sub
    'End With
    'On Error GoTo 0

[COLOR=#0000ff]    Set rango = Range("[/COLOR][COLOR=#ff0000]NamedRange[/COLOR][COLOR=#0000ff]")[/COLOR]
    q = WorksheetFunction.Count(rango)
    
    For i = 2 To q - 1
        If q Mod i = 0 Then
            cad = cad & i & ", "
        End If
    Next
    If cad <> "" Then
        cad = Left(cad, Len(cad) - 2)
    Else
        MsgBox "There are no multiples"
        Exit Sub
    End If
    n = InputBox("Grouping sizes: " & cad, "Write a number")
    If n = "" Then Exit Sub
    n = Val(n)
    nums = Split(cad, ",")
    For nk = 0 To UBound(nums)
        If n = Val(WorksheetFunction.Trim(nums(nk))) Then
            correct = True
            Exit For
        End If
    Next
    If correct = False Then
        MsgBox "Incorrect number"
        Exit Sub
    End If
    j = 1
    k = rango.Cells(1, rango.Columns.Count).Column + 2
    Range(Cells(1, k), Cells(Rows.Count, Columns.Count)).ClearContents
    col = 1
    
    Dim fini, ffin, cini, cfin, f, m
    
    fini = rango.Cells(1, 1).Row
    ffin = rango.Rows.Count + fini - 1
    
    cini = rango.Cells(1, 1).Column
    cfin = rango.Columns.Count + cini - 1
    
    'For Each c In rango
    For m = cini To cfin
        For f = fini To ffin
            Set c = Cells(f, m)
            If c.Value <> "" Then
                rango.Cells(j, k).Value = c.Value
                k = k + 1
                If col = n Then
                    j = j + 1
                    k = rango.Cells(1, rango.Columns.Count).Column + 2
                    col = 0
                End If
                col = col + 1
            End If
        Next
    Next
End Sub

 

[cotech_10]

Hello Dante,

Thank you for the update, it works fine.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.