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,

 

[DanteAmor]

The 45 number multiple number group size combinations would be (5) in total :

1. Combination: 3:4
2. Combination: 6:7
3. Combination: 2:3
4. Combination: 5:10
5. Combination: 8:7
6. 6:9
7. 2:4:9
8. 2:13
9. 2:3:4:6
10. 2:6:7
11. 3:12

And more

P.S. I am new to forum posts etiquette and would like to know how do you simply paste images into a post thread ? I have done so and end up with an image icon and not the intended image I wanted
to directly paste.

Sorry this requires more work time. And it is more complicated than the first requirement, I suggest you create another thread.

To post an image review this:
https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[cotech_10]

Hello Dante,

Thank you for your reply and advice. As suggested I will post another thread with this new request.

Regards

 

[DanteAmor]

Hello Dante,

Thank you for your reply and advice. As suggested I will post another thread with this new request.

Regards

Youre welcome

 

[cotech_10]

Hello Dante,

I was wondering if it was possible with this original designrequirement that instead of analyzing in a horizontal sequence that if you cancreate another macro that would perform the same tasks in a
vertical sequence ?



Thanking You

 

[DanteAmor]

Hello Dante,

I was wondering if it was possible with this original designrequirement that instead of analyzing in a horizontal sequence that if you cancreate another macro that would perform the same tasks in a
vertical sequence ?



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
    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 new code... this works perfectly for vertical analysis.. Great work..

 

[cotech_10]

Hi All,



Based on another thread I had posted on Jun 1st, 2019, 08:04 AM I would like to create a macro for a variation of the original design I had created. A successful macro created and provided by member Dante Amor satisfied the original design requirements as per below:


____ORIGINAL DESIGN REQUIREMENTS______

Ihave 'n' number of rows or lines with number values in various cells 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 fixednumber 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 thedifferent 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 analysis 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 individual 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,










The following VB Code was written to undertake this task:

1. Subnumber_Groups()
2. Dim c As Range, n As Variant, rango AsRange, correct As Boolean

3. Dim nk As Variant, q As Long, cad AsString, col As Long

4. Dim i As Long, j As Long, k As Long, numsAs Variant
5.
6. On Error Resume Next
7. With Application
8. Set rango = .InputBox("selectrange",Default:=Selection.Address, Type:=8)

9. If rango Is Nothing Then Exit Sub
10. End With
11. On Error GoTo 0
12. q = WorksheetFunction.Count(rango)
13.

14. For i = 2 To q - 1
15. If q Mod i = 0 Then
16. cad = cad & i & ","
17. End If
18. Next
19. If cad <> "" Then
20. cad = Left(cad, Len(cad) - 2)

21. Else
22. MsgBox "There are nomultiples"
23. Exit Sub
24. End If
25. n = InputBox("Grouping sizes: "& cad, "Write a number")

26. If n = "" Then Exit Sub
27. n = Val
28. nums = Split(cad, ",")

29. For nk = 0 To UBound(nums)
30. If n =Val(WorksheetFunction.Trim(nums(nk))) Then
31. correct = True
32. Exit For
33. End If

34. Next
35. If correct = False Then
36. MsgBox "Incorrect number"
37. Exit Sub
38. End If
39. j = 1

40. k = rango.Cells(1,rango.Columns.Count).Column + 2
41. Range(Cells(1, k), Cells(Rows.Count,Columns.Count)).ClearContents

42. col = 1
43. For Each c In rango
44. If c.Value <> "" Then
45. rango.Cells(j, k).Value = c.Value
46. k = k + 1
47. If col = n Then
48. j = j + 1
49. k = rango.Cells(1,rango.Columns.Count).Column + 2
50. col = 0
51. End If
52. col = col + 1
53. End If
54. Next
55. EndSub​

This macro works fine for the original requirements but I would like to extend and create a more complex variation to the original design. As per below:


I am looking to analyse multiple group sizes combinations.

For example : Group size combination of say 3 and 4 where the grouping output alternates between grouping sizes, or any combination of group sizes.

Running in the same consecutive manner as mentioned earlier, in the above example it would create the first 3 number group, then create the first 4 number group,then create the second set of 3 number group and then create the second set of 4 numbers..etc......

Looking like this


Group 1: (10,20,30) - 3 set
Group 2: (35,36,99,5) – 4 set
Group 3: (1,202,200) – 3 set
Group 4: (3,6,99,2) – 4 set
Group 5: (4,22,800) – 3 set
etc............


This would still be based on the same different number range selections for eg: either 45 or 35 numbers.

The 45 number multiple number group size combinations would be (5) in total :

1. Combination: 3:4
2. Combination: 6:7
3. Combination: 2:3
4. Combination: 5:10
5. Combination: 8:7

The 35 number multiple number group size combinations would be (3) in total :

1. Combination: 3:4
2. Combination: 2:3
3. Combination: 5:10

I would also like to request that if I could have the opportunity to start with different combinations

For example:

If 45 number pool size and using multiple combination of 3:4 you could change the starting combination to be 4:3.

Thus the 3:4 combinations would result in the following groupings:

Group 1: (10,20,30) - 3 set
Group 2: (35,36,99,5) – 4 set
Group 3: (1,202,200) – 3 set
Group 4: (3,6,99,2) – 4 set
Group 5: (4,22,800) – 3 set
etc............

Changing it to a 4:3 combination would result in the following groupings:


Group 1: (10,20,30,35) - 4 set
Group 2: (36,99,5) – 3 set
Group 3: (1,202,200,3) – 4 set
Group 4: (6,99,2) – 3 set
etc............


Thanking You..!!!! ?
​

 

[DanteAmor]

Hello Dante,

Thank you for the new code... this works perfectly for vertical analysis.. Great work..

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

 

[cotech_10]

Hello Dante,

Once again I would like to thank you for your efforts and could I ask for some reworking of the output when the analysis is completed.
Currently the output result is placed 2 columns to the right of your range of cells. The problem I have encountered is when I run another analysis the previous results are overridden. If I move or copy the results anywhere in any cells to the right of the range of analyzed cells that result is overridden as well.

I would also would like to request if the previous range request can be 'cached' or remembered when another analysis is run.

Thanking You

 

[DanteAmor]

Hello Dante,

Once again I would like to thank you for your efforts and could I ask for some reworking of the output when the analysis is completed.
Currently the output result is placed 2 columns to the right of your range of cells. The problem I have encountered is when I run another analysis the previous results are overridden. If I move or copy the results anywhere in any cells to the right of the range of analyzed cells that result is overridden as well.

I would also would like to request if the previous range request can be 'cached' or remembered when another analysis is run.

Thanking You

One idea is that you copy the result you want to save on another sheet.