| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
group (add) numbers without exceeding 168
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 -- Thank You in advance, Zab |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
group (add) numbers without exceeding 168
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" wrote: 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 -- Thank You in advance, Zab |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
group (add) numbers without exceeding 168
i am receiving a compile and syntax error in this line...
grpNumb = CDbl(Format(lstRw/168, "#0.000.0") -- Thank You, Zab "JLGWhiz" wrote: 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" wrote: 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 -- Thank You in advance, Zab |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
group (add) numbers without exceeding 168
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 "Zab" wrote: 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 -- Thank You in advance, Zab |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
group (add) numbers without exceeding 168
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 "Zab" wrote: i am receiving a compile and syntax error in this line... grpNumb = CDbl(Format(lstRw/168, "#0.000.0") -- Thank You, Zab "JLGWhiz" wrote: 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" wrote: 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 -- Thank You in advance, Zab |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
group (add) numbers without exceeding 168
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" wrote: i am receiving a compile and syntax error in this line... grpNumb = CDbl(Format(lstRw/168, "#0.000.0") -- Thank You, Zab "JLGWhiz" wrote: 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" wrote: 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 -- Thank You in advance, Zab |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
group (add) numbers without exceeding 168
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. -- Thank You, Zab "Mike H" wrote: 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 "Zab" wrote: 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 -- Thank You in advance, Zab |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
group (add) numbers without exceeding 168
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. -- Thank You, Zab "Zab" wrote: 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. -- Thank You, Zab "Mike H" wrote: 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 "Zab" wrote: 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 -- Thank You in advance, Zab |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007-digit separator ',' for numbers/currency exceeding 999, | Excel Discussion (Misc queries) | |||
| group (add) numbers without exceeding 168" the best possible way. | Excel Discussion (Misc queries) | |||
| How can I convert a group of numbers to a group of letters? | Excel Worksheet Functions | |||
| Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
| Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Linear Mode
