group (add) numbers without exceeding 168

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Zab - 24 Jan 2008 18:33 GMT

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

Signature

Thank You in advance, Zab

Reply to this Message

JLGWhiz - 24 Jan 2008 18:55 GMT

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.

> 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
[quoted text clipped - 39 lines]
>
> 30.883 30.883 23.5 = 85.266

Reply to this Message

Zab - 24 Jan 2008 19:23 GMT

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

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

Signature

Thank You, Zab

> Assume that the numbers are in Column A and there are no empty rows.
>
[quoted text clipped - 50 lines]
> >
> > 30.883 30.883 23.5 = 85.266

Reply to this Message

JLGWhiz - 24 Jan 2008 21:18 GMT

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

> i am receiving a compile and syntax error in this line...
>
[quoted text clipped - 54 lines]
> > >
> > > 30.883 30.883 23.5 = 85.266

Reply to this Message

JLGWhiz - 24 Jan 2008 21:26 GMT

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

> i am receiving a compile and syntax error in this line...
>
[quoted text clipped - 54 lines]
> > >
> > > 30.883 30.883 23.5 = 85.266

Reply to this Message

Mike H - 24 Jan 2008 19:45 GMT

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

Reply to this Message

Zab - 25 Jan 2008 12:03 GMT

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.

Signature

Thank You, Zab

> On the assumption there are no other rules except not exceeding 168 then try
> this
[quoted text clipped - 60 lines]
> >
> > 30.883 30.883 23.5 = 85.266

Reply to this Message

Zab - 25 Jan 2008 16:15 GMT

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.

Signature

Thank You, Zab

> I thank you both very much for your help!
>
[quoted text clipped - 68 lines]
> > >
> > > 30.883 30.883 23.5 = 85.266

Reply to this Message