group (add) numbers without exceeding 168

[Zab]

hello i am triing to group these numbers below, but i do not want the total
to exceed 168. i need to know how many pieces that are 168 in length it will
take. i would imagine it will take some vba to conduct this and i am fine
with this.

23.5
23.5
30.883
30.883
30.883
30.883
37.383
37.383
43.411
43.411
47
48
49.876
50.646
50.883
50.883
50.883
50.883
50.883
50.883
50.883
50.883
55.383
55.383
59.5
90


below i have manually grouped the numbers together with their totals to the
right.
50.883 50.883 50.883 = 152.649
50.883 50.883 50.883 = 152.649
48 43.411 30.883 30.883 = 153.177
55.383 50.883 50.883 = 157.149
50.646 47 43.411 23.5 = 164.557
59.5 55.383 49.876 = 164.759
90 37.383 37.383 = 164.766

30.883 30.883 23.5 = 85.266

 

[JLGWhiz]

Assume that the numbers are in Column A and there are no empty rows.

Sub grp168()
lstRw = Cells(Rows.Count, 1).End(xlUP).Row
grpNumb = CDbl(Format(lstRw/168, "#0.000.0")
MsgBox "You can organize " & grpNumb & _
"Groups of 168 items.",, "Groups"
End Sub

I don't understand the second part of your posting.

 

[Zab]

i am receiving a compile and syntax error in this line...

grpNumb = CDbl(Format(lstRw/168, "#0.000.0")

 

[Mike H]

On the assumption there are no other rules except not exceeding 168 then try
this

Sub lime()
For x = 1 To Cells(Rows.Count, "A").End(xlUp).Row
grandtotal = grandtotal + Cells(x, 1).Value
nextvalue = Cells(x, 1).Value
If total + nextvalue <= 168 Then
total = total + nextvalue
Else
Sum = Sum + total
total = nextvalue
Count = Count + 1
End If
Next
If Sum < grandtotal Then Count = Count + 1
MsgBox Count & " Groups of 168 or less"
End Sub

Mike

 

[JLGWhiz]

I was trying to show that there might be an odd lot and hold it to one
decimal point. This will show the number of Whole lots of 168 you can get
and an odd lot carried to several decimal places.

Sub grp168()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
doMath = lstRw / 168
grpNumb = CDbl(doMath)
MsgBox "You can organize " & grpNumb & _
" Groups of 168 items.", , "Groups"
End Sub

 

[JLGWhiz]

Actually, after I corrected the typos, this one worked right.

Sub grp168()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
grpNumb = CDbl(Format(lstRw / 168, "#0,000.0"))
MsgBox "You can organize " & grpNumb & _
" Groups of 168 items.", , "Groups"
End Sub

 

[Zab]

I thank you both very much for your help!

Now that i look at it closer do you think i could get the out come to look
like what i have shown after a manual grouping. Then I need it to tell me how
many pieces I need at 168 (14'). If there is a total below 120 (10') I would
need it to tell me. The answer i am looking for here with these numbers is:
7-14' & 1-10'. Sorry for the confusion. Thanks again.

 

[Zab]

another example:
here are the numbers i need to allocate into 14' pieces.
15.968
24.453
39.656
39.656
39.656
39.656
39.656
39.656
39.656
39.656
39.656
39.656
39.656
39.656
41.423
41.423
41.423
41.423
50.616
50.616
72.183
73.621
85.618
85.618
101.017
101.017
101.017
101.017
110.488
110.498
116.773
116.774
127.340
127.340
130.340
130.340
130.340
130.340
142.736
142.736
144.429
144.429
150.689
150.689

MANUALLY GROUPED
TOTAL
130.34 130.34
130.34 130.34
130.34 130.34
130.34 130.34
101.017 39.656 140.673
101.017 39.656 140.673
142.736 142.736
144.429 144.429
144.429 144.429
110.488 39.656 150.144
110.498 39.656 150.154
150.689 150.689
101.017 50.616 151.633
101.017 50.616 151.633
116.773 39.656 156.429
116.774 39.656 156.43
85.618 72.183 157.801
85.618 73.621 159.239
41.423 39.656 41.423 39.656 162.158
41.423 39.656 41.423 39.656 162.158
150.689 15.968 166.657
127.34 39.656 166.996
127.34 39.656 166.996
142.736 24.453 167.189

required pieces: 24-14'
no 10' pieces required.