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 DanteAmor 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:
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 alternatesbetween 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) intotal :
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 thestarting 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..!!!! ?
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 DanteAmor 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
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 alternatesbetween 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) intotal :
- Combination: 3:4
- Combination: 6:7
- Combination: 2:3
- Combination: 5:10
- Combination: 8:7
- Combination: 3:4
- Combination: 2: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 thestarting 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..!!!! ?
