Excel number Groups

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
135
Office Version
  1. 2016
Platform
  1. Windows
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.

zu3sYgAAAABJRU5ErkJggg==




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,
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Put your numbers starting in cell A1 as shown:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">79</td><td style="text-align:right; ">384</td><td style="text-align:right; ">423</td><td style="text-align:right; ">99</td><td style="text-align:right; ">394</td><td style="text-align:right; ">129</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">251</td><td style="text-align:right; ">368</td><td style="text-align:right; ">433</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">420</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">260</td><td style="text-align:right; ">117</td><td style="text-align:right; ">121</td><td style="text-align:right; ">373</td><td style="text-align:right; ">470</td><td style="text-align:right; ">45</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">43</td><td style="text-align:right; ">250</td><td style="text-align:right; ">152</td><td style="text-align:right; ">124</td><td style="text-align:right; ">137</td><td style="background-color:#92d050; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">257</td><td style="text-align:right; ">232</td><td style="text-align:right; ">124</td><td style="text-align:right; ">198</td><td style="text-align:right; ">89</td><td style="text-align:right; ">14</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">149</td><td style="text-align:right; ">418</td><td style="text-align:right; ">370</td><td style="text-align:right; ">239</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">54</td><td style="text-align:right; ">237</td><td style="text-align:right; ">38</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">429</td><td style="text-align:right; ">335</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">424</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">379</td><td style="text-align:right; ">301</td><td style="text-align:right; ">474</td><td style="text-align:right; ">76</td><td style="text-align:right; ">20</td><td style="background-color:#92d050; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">129</td><td style="text-align:right; ">312</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td></tr></table>



Run this macro

Code:
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", 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
    For Each c In rango
        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
End Sub

It works like this:


A window appears to select the range of cells.
cbe21e09a171011ef8d2489e6ab73454.jpg

Press Ok

---


Then another window appears to show the number of Grouping sizes

bb38000d85a425cda5ee7fed244f5c5e.jpg

Press Ok

----
The result will be 2 columns to the right of your range of cells.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff99; text-align:right; ">79</td><td style="background-color:#ffff99; text-align:right; ">384</td><td style="background-color:#ffff99; text-align:right; ">423</td><td style="background-color:#ffff99; text-align:right; ">99</td><td style="background-color:#ffff99; text-align:right; ">394</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff99; text-align:right; ">129</td><td style="background-color:#ffff99; text-align:right; ">251</td><td style="background-color:#ffff99; text-align:right; ">368</td><td style="background-color:#ffff99; text-align:right; ">433</td><td style="background-color:#ffff99; text-align:right; ">420</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff99; text-align:right; ">1</td><td style="background-color:#ffff99; text-align:right; ">260</td><td style="background-color:#ffff99; text-align:right; ">117</td><td style="background-color:#ffff99; text-align:right; ">121</td><td style="background-color:#ffff99; text-align:right; ">373</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffff99; text-align:right; ">470</td><td style="background-color:#ffff99; text-align:right; ">45</td><td style="background-color:#ffff99; text-align:right; ">43</td><td style="background-color:#ffff99; text-align:right; ">250</td><td style="background-color:#ffff99; text-align:right; ">152</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffff99; text-align:right; ">124</td><td style="background-color:#ffff99; text-align:right; ">137</td><td style="background-color:#ffff99; text-align:right; ">257</td><td style="background-color:#ffff99; text-align:right; ">232</td><td style="background-color:#ffff99; text-align:right; ">124</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffff99; text-align:right; ">198</td><td style="background-color:#ffff99; text-align:right; ">89</td><td style="background-color:#ffff99; text-align:right; ">14</td><td style="background-color:#ffff99; text-align:right; ">149</td><td style="background-color:#ffff99; text-align:right; ">418</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffff99; text-align:right; ">370</td><td style="background-color:#ffff99; text-align:right; ">239</td><td style="background-color:#ffff99; text-align:right; ">54</td><td style="background-color:#ffff99; text-align:right; ">237</td><td style="background-color:#ffff99; text-align:right; ">38</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffff99; text-align:right; ">429</td><td style="background-color:#ffff99; text-align:right; ">335</td><td style="background-color:#ffff99; text-align:right; ">424</td><td style="background-color:#ffff99; text-align:right; ">379</td><td style="background-color:#ffff99; text-align:right; ">301</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffff99; text-align:right; ">474</td><td style="background-color:#ffff99; text-align:right; ">76</td><td style="background-color:#ffff99; text-align:right; ">20</td><td style="background-color:#ffff99; text-align:right; ">129</td><td style="background-color:#ffff99; text-align:right; ">312</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td><td style="background-color:#ffff99; "> </td></tr></table>
 
Upvote 0
Hi Dante,
 
Thank you for your amazing creation and response, it is greatly appreciated.
The expected outcome is as designed and requested except there seems to be an issue
with running the macro in another Sheet in the same workbook. For example when I run the
macro in "Sheet1" I do get the correct Dialog boxes popping up to specify the
data range and the grouping number size. However when I run the same macro in say
"Sheet2" the dialog box that prompts the user to enter the number grouping size
is incorrect. The values presented are "Grouping sizes:2,4,11,22".
 
As well are you also able to expand the Grouping sizes to include group sizes to include a
grouping size of 7 ?
 
 
In a different request to the above I do have another request VARIANT attached to this number group analysis and that is would be
analysis and that is would it be possible to create a separate macro thatexplores multiple group sizes combinations ?
 
For example : Group size combination of 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 groups, 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)
Group 2: (35,36,99,5)
Group 3: (1,202,200)
Group 4: (3,6,99,2)
Group 5: (4,22,800)

etc............


 
Upvote 0
Hello Dante,

I think the issue is also that it maybe the grouping values are dependent ontheir being 45 numbers present in the data range that gets selected.
It seems that if you have selected less than 45 numbers you get a different setof grouping range values. Is there any way to keep the grouping size values “fixed”no matter what range or how many numbers get selected in the dialog box whereyou specify the range ?



Cheers

 
Upvote 0
Hi Dante,



Sorry about inundating you with these posts, but I am working through and testing the macro you have created and establishing how it works. The grouping size values displayed on the dialog box for input of a group size is of course determinant on the Pool size of the total numbers that are being analyzed. Inthe design I gave you my pool size was 45 numbers and the groupings of 3, 5, 9& 15 are equal divisible multiples that go evenly into a pool of 45 number.

I guess I should of made it clearer and specified an additional macro thatwould perform analysis on a pool size of 35 numbers. Thus the group size valueswould be only the following divisible multiples of 7 and 5.

I hope I have not caused too much confusion but if you could create a macro along that reduced pool size of 35 numbers and create also a macro that has been mentioned in previous communication of alternate group sizes that would be appreciated.

Thanking You
 
Upvote 0
I guess I should of made it clearer and specified an additional macro thatwould perform analysis on a pool size of 35 numbers. Thus the group size valueswould be only the following divisible multiples of 7 and 5.

If you select a range of cells where there are 35 numbers, the macro asks you for multiples 5 or 7


ee9770831120434eb6dbe90269293bcd.jpg

If you write 7, the macro generates 5 groups of 7 numbers.

Up to this point, is something missing from the macro?

-----
We gladly make some changes to the macro, but I would like to close the original requirement, and then, if necessary, create new macros.
 
Upvote 0
Hello Dante,

Once again thank you for the explanation of how this macro works and can now see how it can be
Implemented for different pool sizes, fantastic work.

I would like to close off this request and will be submitting another request for a different macro based on a variant of the original design of this macro, this will be submitted today sometime.

Thanking you in your assistance, it is greatly appreciated.


Regards

:-)
 
Upvote 0
Hello Dante,


As per the last conversation I would like to submit another request foranother macro with modifications to the design of the original number groups macro.


I am looking to analyse multiple group sizes combinations.

For example : Group sizecombination 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 selectionsfor 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..!!!! ?

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.
 
Upvote 0
Hello Dante,

Once again thank you for the explanation of how this macro works and can now see how it can be
Implemented for different pool sizes, fantastic work.

I would like to close off this request and will be submitting another request for a different macro based on a variant of the original design of this macro, this will be submitted today sometime.

Thanking you in your assistance, it is greatly appreciated.


Regards

:-)

I'm glad to help you. Thanks for the feedback.
 
Upvote 0
Hello Dante,

Thank you once again for your help and look forward in hearing back from you soon on my second
request...

Cheers
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top