Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
Morning,
I have a problem which I have tried to post on the functions forum but no answer. Its difficult to explain so Im not sure if people are understanding what Im after. In order to make copper wire it is wound onto bobines which can support a maximum weight of 400kg. The machine that makes the bobines is very fast so if it can avoided its better not to make bobines smaller than 100kg, however if we must we can. If a customer needs 600kg of wire they will get a bobine of 400kg and another of 200kg. To make it more difficult each product can be based on several sub compositions. First of all Ill explain it for the products made from single components if thats not understood then the full solution will be completely lost. Single components- What I would like is a formula/macro that can look through the data and group the products that are the same. In col A of the sheet named (Bobines) I have my product list (A,B,C) in col B I have the individual weights of the bobines required. In col C,D,E¦¦ I would like to generate the results the individual weights of the bobines to be produced. The sequence is match the products, sum the weights then if: - the sum is less than 400kg continue to add weights until the limit of 400 is reached - the sum (or a component) is over 400kg continue adding until the orders can be split into 100kg< bobine weight < 400kg. - if no sum is possible just write the weight of the bobine as per the original customer order. - Where weights are being added to the next bobine just write nothing (blank or ) as a result - write the cumulative weights in the cell adjacent to the last bobine that was added (Just for ref these bobines are not actually sold then are then fed into other processes so they will be resplit into the original orders further down the line after additional work). Results Product Customer Wt Bob 1 Bob 2 B 390 390 B 90 90 C 400 400 C 90 90 A 420 --- A 90 400 110 D 600 ---- D 90 400 290 B 50 ---- B 100 150 Multiple components The added difficulty here is that the formula/code needs to look in several places to produce the result as it can match with the product in col A OR col C AND continue as long vertically as it wants until it reaches the limits of 100kg < xx < 400kg. In total there are four positions in which to match between consecutive rows (A-A, A-C, C-A and C-C). Results A 100 --- --- --- --- A 100 --- --- --- --- A 50 A 75 --- --- A 425 B 150 B 55 --- --- --- --- B 175 C 65 B 380 --- --- B 55 C 75 B 55 --- --- C 125 D 85 C 200 D 85 B 20 A 50 B 20 A 50 In both cases only items that are adjacent on the list should be grouped irrespective of weights, (hence why the last group of A&Bs are not grouped with the first lot). It is the second case of multiple components that I need to get to as a working result. Is this iterative procedure possible? I guess a macro is the easiest way to go otherwise itll be a never ending list of IF formulas. Thanks LiAD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
I can't get my head around this I'm afraid.
We need an algorithm. That is, a sequence of logical steps ... You're very close to this project, so it woudl help maybe if you took a step back and started as if we're total beginners - which we are ;) thanks "LiAD" wrote: Morning, I have a problem which I have tried to post on the functions forum but no answer. Its difficult to explain so Im not sure if people are understanding what Im after. In order to make copper wire it is wound onto bobines which can support a maximum weight of 400kg. The machine that makes the bobines is very fast so if it can avoided its better not to make bobines smaller than 100kg, however if we must we can. If a customer needs 600kg of wire they will get a bobine of 400kg and another of 200kg. To make it more difficult each product can be based on several sub compositions. First of all Ill explain it for the products made from single components if thats not understood then the full solution will be completely lost. Single components- What I would like is a formula/macro that can look through the data and group the products that are the same. In col A of the sheet named (Bobines) I have my product list (A,B,C) in col B I have the individual weights of the bobines required. In col C,D,E¦¦ I would like to generate the results the individual weights of the bobines to be produced. The sequence is match the products, sum the weights then if: - the sum is less than 400kg continue to add weights until the limit of 400 is reached - the sum (or a component) is over 400kg continue adding until the orders can be split into 100kg< bobine weight < 400kg. - if no sum is possible just write the weight of the bobine as per the original customer order. - Where weights are being added to the next bobine just write nothing (blank or ) as a result - write the cumulative weights in the cell adjacent to the last bobine that was added (Just for ref these bobines are not actually sold then are then fed into other processes so they will be resplit into the original orders further down the line after additional work). Results Product Customer Wt Bob 1 Bob 2 B 390 390 B 90 90 C 400 400 C 90 90 A 420 --- A 90 400 110 D 600 ---- D 90 400 290 B 50 ---- B 100 150 Multiple components The added difficulty here is that the formula/code needs to look in several places to produce the result as it can match with the product in col A OR col C AND continue as long vertically as it wants until it reaches the limits of 100kg < xx < 400kg. In total there are four positions in which to match between consecutive rows (A-A, A-C, C-A and C-C). Results A 100 --- --- --- --- A 100 --- --- --- --- A 50 A 75 --- --- A 425 B 150 B 55 --- --- --- --- B 175 C 65 B 380 --- --- B 55 C 75 B 55 --- --- C 125 D 85 C 200 D 85 B 20 A 50 B 20 A 50 In both cases only items that are adjacent on the list should be grouped irrespective of weights, (hence why the last group of A&Bs are not grouped with the first lot). It is the second case of multiple components that I need to get to as a working result. Is this iterative procedure possible? I guess a macro is the easiest way to go otherwise itll be a never ending list of IF formulas. Thanks LiAD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
This problem is very simple once you understand the algorithm. There is an
implied requirement to use the least number of number of bobines. So you want to to take the weight and divide by the maximum weight. This gives you only three diffferent cases Let W = Weight of order 1) The weight is less than the minimum wieght. Solution: 1 bobine of the ordered weight W. 2) You divide the weight by the max weight and analize the remainder. If the remainder is greater than the minimum you have your solution Solution: Number of bobines is N = Int(W/400) + 1 Int is the integer part of the division. You will have N bobines of 400 and 1 bobine of weight mod(W,400) where mod is the remainder of the division W/400 3) The same as case 2 except the remainder is less than the minimum. In this case you take 100 off the last roll and then add mod(W,400) Solution : Number of bobines is N = Int(W/400) + 1 You will have N-2 bobines of 400 You will have 1 bobine of 400 - 100 = 300 You will have 1 bobine of 100 + mod(W,400) "Patrick Molloy" wrote: I can't get my head around this I'm afraid. We need an algorithm. That is, a sequence of logical steps ... You're very close to this project, so it woudl help maybe if you took a step back and started as if we're total beginners - which we are ;) thanks "LiAD" wrote: Morning, I have a problem which I have tried to post on the functions forum but no answer. Its difficult to explain so Im not sure if people are understanding what Im after. In order to make copper wire it is wound onto bobines which can support a maximum weight of 400kg. The machine that makes the bobines is very fast so if it can avoided its better not to make bobines smaller than 100kg, however if we must we can. If a customer needs 600kg of wire they will get a bobine of 400kg and another of 200kg. To make it more difficult each product can be based on several sub compositions. First of all Ill explain it for the products made from single components if thats not understood then the full solution will be completely lost. Single components- What I would like is a formula/macro that can look through the data and group the products that are the same. In col A of the sheet named (Bobines) I have my product list (A,B,C) in col B I have the individual weights of the bobines required. In col C,D,E¦¦ I would like to generate the results the individual weights of the bobines to be produced. The sequence is match the products, sum the weights then if: - the sum is less than 400kg continue to add weights until the limit of 400 is reached - the sum (or a component) is over 400kg continue adding until the orders can be split into 100kg< bobine weight < 400kg. - if no sum is possible just write the weight of the bobine as per the original customer order. - Where weights are being added to the next bobine just write nothing (blank or ) as a result - write the cumulative weights in the cell adjacent to the last bobine that was added (Just for ref these bobines are not actually sold then are then fed into other processes so they will be resplit into the original orders further down the line after additional work). Results Product Customer Wt Bob 1 Bob 2 B 390 390 B 90 90 C 400 400 C 90 90 A 420 --- A 90 400 110 D 600 ---- D 90 400 290 B 50 ---- B 100 150 Multiple components The added difficulty here is that the formula/code needs to look in several places to produce the result as it can match with the product in col A OR col C AND continue as long vertically as it wants until it reaches the limits of 100kg < xx < 400kg. In total there are four positions in which to match between consecutive rows (A-A, A-C, C-A and C-C). Results A 100 --- --- --- --- A 100 --- --- --- --- A 50 A 75 --- --- A 425 B 150 B 55 --- --- --- --- B 175 C 65 B 380 --- --- B 55 C 75 B 55 --- --- C 125 D 85 C 200 D 85 B 20 A 50 B 20 A 50 In both cases only items that are adjacent on the list should be grouped irrespective of weights, (hence why the last group of A&Bs are not grouped with the first lot). It is the second case of multiple components that I need to get to as a working result. Is this iterative procedure possible? I guess a macro is the easiest way to go otherwise itll be a never ending list of IF formulas. Thanks LiAD |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
Yeah I know Im too close. Ill try more simply.
Copper wire is made in certain diameters, 0.2mm, 0.3mm, 0.4mm etc. In order to transport the wire is wound onto bobines. Each bobine size has a maximum weight that it can support 400kg in this case. Imagine a two stage process where u make the wire xx diameter then u send it to another machine to get the plastic put around the outside. The output from the plastic wrapping machine will also be wound onto bobines. Customer A would like 200kg, B 100kg, C 50kg all of exactly the same wire, 0.3mm with a red plastic coat. On machine 1 to save time, bobines, space etc we will make one big bobine of 350kg of 0.3mm wire to send the wrapping machine. The wire will be wrapped in red plastic and wound onto three separate bobines (200,100,50kg). If we dont combine the orders on the first machine we need more bobines, more stopping/starting of the machine, more set-ups, more time etc etc. By combining the customer gets what they need and we reduce our timescale. Life not being so easy they dont all want the same thing of course. They want different diameters, colours and compositions. Compositions???? Some customers dont want one big wire of 0.2mm diameter, they want two wires a smaller diameter stuck together to make one of 0.2mm. So on machine 1 instead of making 200kg of 0.2mm it needs to make 150kg of 0.07mm + 50kg of 0.05mm or whatever is needed. The product code I referred to in the last post (A,B,C) can be thought of as wire diameter. What I would like is a method of excel deciding which bobines can be grouped and which cannot. In order to be grouped they must have the same diameter AND be sequential on the list. If they cant be grouped, too bad for us, we make it on smaller bobines. If one customer wants 600kg in any case he will be forced to take two bobines, one of 400kg and one of 200kg. If however the next customer wants the same diameter for 100kg we can combine that with the second bobine of 200kg we will make for the first customer so the factory would make 400kg and a 300kg bobine on machine 1. If however one customer wanted 390kg and another 50kg we do not want to make one 400kg and one of 40kg as the first customer can have his 390 but the second will have a 10 with a 40 joined to it not very useful in cables. Third case is if the first customer wants more than 420kg and the second 50kg we do not want to combine the orders as this would create a very small second bobine of 70kg. In this case we need do something special to make a 420kg and a 50kg costly and time consuming but thats life. So the logic is - test if diameters (A,B,C etc) are the same from cols A and C - if false return the original bobine weight - if true add the bobine weights and test if =<400, o if true add the next bobine weight, retest to see if the cumulative wght =<400 and this continues until it hits the limit ļ§ when it hits the limit it writes the bobine weights to be produced opposite the last item, o if false test to see if all the bobines are between 100 and 400kg. ļ§ if no more additions possible divides the bobines weights as required by the grouping and customers opposite the last item. ļ§ if can add more continue until it hits the limit then write the individual bobine weights opposite the last item Does that (quite long, maybe too long) description make it any easier? "Patrick Molloy" wrote: I can't get my head around this I'm afraid. We need an algorithm. That is, a sequence of logical steps ... You're very close to this project, so it woudl help maybe if you took a step back and started as if we're total beginners - which we are ;) thanks "LiAD" wrote: Morning, I have a problem which I have tried to post on the functions forum but no answer. Its difficult to explain so Im not sure if people are understanding what Im after. In order to make copper wire it is wound onto bobines which can support a maximum weight of 400kg. The machine that makes the bobines is very fast so if it can avoided its better not to make bobines smaller than 100kg, however if we must we can. If a customer needs 600kg of wire they will get a bobine of 400kg and another of 200kg. To make it more difficult each product can be based on several sub compositions. First of all Ill explain it for the products made from single components if thats not understood then the full solution will be completely lost. Single components- What I would like is a formula/macro that can look through the data and group the products that are the same. In col A of the sheet named (Bobines) I have my product list (A,B,C) in col B I have the individual weights of the bobines required. In col C,D,E¦¦ I would like to generate the results the individual weights of the bobines to be produced. The sequence is match the products, sum the weights then if: - the sum is less than 400kg continue to add weights until the limit of 400 is reached - the sum (or a component) is over 400kg continue adding until the orders can be split into 100kg< bobine weight < 400kg. - if no sum is possible just write the weight of the bobine as per the original customer order. - Where weights are being added to the next bobine just write nothing (blank or ) as a result - write the cumulative weights in the cell adjacent to the last bobine that was added (Just for ref these bobines are not actually sold then are then fed into other processes so they will be resplit into the original orders further down the line after additional work). Results Product Customer Wt Bob 1 Bob 2 B 390 390 B 90 90 C 400 400 C 90 90 A 420 --- A 90 400 110 D 600 ---- D 90 400 290 B 50 ---- B 100 150 Multiple components The added difficulty here is that the formula/code needs to look in several places to produce the result as it can match with the product in col A OR col C AND continue as long vertically as it wants until it reaches the limits of 100kg < xx < 400kg. In total there are four positions in which to match between consecutive rows (A-A, A-C, C-A and C-C). Results A 100 --- --- --- --- A 100 --- --- --- --- A 50 A 75 --- --- A 425 B 150 B 55 --- --- --- --- B 175 C 65 B 380 --- --- B 55 C 75 B 55 --- --- C 125 D 85 C 200 D 85 B 20 A 50 B 20 A 50 In both cases only items that are adjacent on the list should be grouped irrespective of weights, (hence why the last group of A&Bs are not grouped with the first lot). It is the second case of multiple components that I need to get to as a working result. Is this iterative procedure possible? I guess a macro is the easiest way to go otherwise itll be a never ending list of IF formulas. Thanks LiAD |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
the code below give the first table results. the code is checking only
column A to see if products match. I have to look at the 2nd table. will get back to you later Sub CombineOrders() With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row PreviousOrder = 0 For RowCount = 2 To LastRow Order = .Range("B" & RowCount) Order = Order + PreviousOrder If PreviousOrder = 0 Then If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then 'product matches NextOrder = .Range("B" & (RowCount + 1)) If Order <= 400 Then If Order + NextOrder <= 400 Then PreviousOrder = Order Else .Range("C" & RowCount) = Order End If Else PreviousOrder = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Else 'previous order doesn't match don't not combine 3 rows If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Next RowCount End With End Sub "LiAD" wrote: Yeah I know Im too close. Ill try more simply. Copper wire is made in certain diameters, 0.2mm, 0.3mm, 0.4mm etc. In order to transport the wire is wound onto bobines. Each bobine size has a maximum weight that it can support 400kg in this case. Imagine a two stage process where u make the wire xx diameter then u send it to another machine to get the plastic put around the outside. The output from the plastic wrapping machine will also be wound onto bobines. Customer A would like 200kg, B 100kg, C 50kg all of exactly the same wire, 0.3mm with a red plastic coat. On machine 1 to save time, bobines, space etc we will make one big bobine of 350kg of 0.3mm wire to send the wrapping machine. The wire will be wrapped in red plastic and wound onto three separate bobines (200,100,50kg). If we dont combine the orders on the first machine we need more bobines, more stopping/starting of the machine, more set-ups, more time etc etc. By combining the customer gets what they need and we reduce our timescale. Life not being so easy they dont all want the same thing of course. They want different diameters, colours and compositions. Compositions???? Some customers dont want one big wire of 0.2mm diameter, they want two wires a smaller diameter stuck together to make one of 0.2mm. So on machine 1 instead of making 200kg of 0.2mm it needs to make 150kg of 0.07mm + 50kg of 0.05mm or whatever is needed. The product code I referred to in the last post (A,B,C) can be thought of as wire diameter. What I would like is a method of excel deciding which bobines can be grouped and which cannot. In order to be grouped they must have the same diameter AND be sequential on the list. If they cant be grouped, too bad for us, we make it on smaller bobines. If one customer wants 600kg in any case he will be forced to take two bobines, one of 400kg and one of 200kg. If however the next customer wants the same diameter for 100kg we can combine that with the second bobine of 200kg we will make for the first customer so the factory would make 400kg and a 300kg bobine on machine 1. If however one customer wanted 390kg and another 50kg we do not want to make one 400kg and one of 40kg as the first customer can have his 390 but the second will have a 10 with a 40 joined to it not very useful in cables. Third case is if the first customer wants more than 420kg and the second 50kg we do not want to combine the orders as this would create a very small second bobine of 70kg. In this case we need do something special to make a 420kg and a 50kg costly and time consuming but thats life. So the logic is - test if diameters (A,B,C etc) are the same from cols A and C - if false return the original bobine weight - if true add the bobine weights and test if =<400, o if true add the next bobine weight, retest to see if the cumulative wght =<400 and this continues until it hits the limit ļ§ when it hits the limit it writes the bobine weights to be produced opposite the last item, o if false test to see if all the bobines are between 100 and 400kg. ļ§ if no more additions possible divides the bobines weights as required by the grouping and customers opposite the last item. ļ§ if can add more continue until it hits the limit then write the individual bobine weights opposite the last item Does that (quite long, maybe too long) description make it any easier? "Patrick Molloy" wrote: I can't get my head around this I'm afraid. We need an algorithm. That is, a sequence of logical steps ... You're very close to this project, so it woudl help maybe if you took a step back and started as if we're total beginners - which we are ;) thanks "LiAD" wrote: Morning, I have a problem which I have tried to post on the functions forum but no answer. Its difficult to explain so Im not sure if people are understanding what Im after. In order to make copper wire it is wound onto bobines which can support a maximum weight of 400kg. The machine that makes the bobines is very fast so if it can avoided its better not to make bobines smaller than 100kg, however if we must we can. If a customer needs 600kg of wire they will get a bobine of 400kg and another of 200kg. To make it more difficult each product can be based on several sub compositions. First of all Ill explain it for the products made from single components if thats not understood then the full solution will be completely lost. Single components- What I would like is a formula/macro that can look through the data and group the products that are the same. In col A of the sheet named (Bobines) I have my product list (A,B,C) in col B I have the individual weights of the bobines required. In col C,D,E¦¦ I would like to generate the results the individual weights of the bobines to be produced. The sequence is match the products, sum the weights then if: - the sum is less than 400kg continue to add weights until the limit of 400 is reached - the sum (or a component) is over 400kg continue adding until the orders can be split into 100kg< bobine weight < 400kg. - if no sum is possible just write the weight of the bobine as per the original customer order. - Where weights are being added to the next bobine just write nothing (blank or ) as a result - write the cumulative weights in the cell adjacent to the last bobine that was added (Just for ref these bobines are not actually sold then are then fed into other processes so they will be resplit into the original orders further down the line after additional work). Results Product Customer Wt Bob 1 Bob 2 B 390 390 B 90 90 C 400 400 C 90 90 A 420 --- A 90 400 110 D 600 ---- D 90 400 290 B 50 ---- B 100 150 Multiple components The added difficulty here is that the formula/code needs to look in several places to produce the result as it can match with the product in col A OR col C AND continue as long vertically as it wants until it reaches the limits of 100kg < xx < 400kg. In total there are four positions in which to match between consecutive rows (A-A, A-C, C-A and C-C). Results A 100 --- --- --- --- A 100 --- --- --- --- A 50 A 75 --- --- A 425 B 150 B 55 --- --- --- --- B 175 C 65 B 380 --- --- B 55 C 75 B 55 --- --- C 125 D 85 C 200 D 85 B 20 A 50 B 20 A 50 In both cases only items that are adjacent on the list should be grouped irrespective of weights, (hence why the last group of A&Bs are not grouped with the first lot). It is the second case of multiple components that I need to get to as a working result. Is this iterative procedure possible? I guess a macro is the easiest way to go otherwise itll be a never ending list of IF formulas. Thanks LiAD |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
Hi,
Thanks a lot for your help. Relief to find that maybe a way is possible! One thing I've noticed is that this code stops after 2 rows. So the inputs and result for the following is A 50 A 50 100 A 200 200 whereas the desired output is A 50 A 50 A 200 300 I guess its better to know now rather than later. Thanks a lot for your help again LiAD "Joel" wrote: the code below give the first table results. the code is checking only column A to see if products match. I have to look at the 2nd table. will get back to you later Sub CombineOrders() With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row PreviousOrder = 0 For RowCount = 2 To LastRow Order = .Range("B" & RowCount) Order = Order + PreviousOrder If PreviousOrder = 0 Then If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then 'product matches NextOrder = .Range("B" & (RowCount + 1)) If Order <= 400 Then If Order + NextOrder <= 400 Then PreviousOrder = Order Else .Range("C" & RowCount) = Order End If Else PreviousOrder = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Else 'previous order doesn't match don't not combine 3 rows If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Next RowCount End With End Sub "LiAD" wrote: Yeah I know Im too close. Ill try more simply. Copper wire is made in certain diameters, 0.2mm, 0.3mm, 0.4mm etc. In order to transport the wire is wound onto bobines. Each bobine size has a maximum weight that it can support 400kg in this case. Imagine a two stage process where u make the wire xx diameter then u send it to another machine to get the plastic put around the outside. The output from the plastic wrapping machine will also be wound onto bobines. Customer A would like 200kg, B 100kg, C 50kg all of exactly the same wire, 0.3mm with a red plastic coat. On machine 1 to save time, bobines, space etc we will make one big bobine of 350kg of 0.3mm wire to send the wrapping machine. The wire will be wrapped in red plastic and wound onto three separate bobines (200,100,50kg). If we dont combine the orders on the first machine we need more bobines, more stopping/starting of the machine, more set-ups, more time etc etc. By combining the customer gets what they need and we reduce our timescale. Life not being so easy they dont all want the same thing of course. They want different diameters, colours and compositions. Compositions???? Some customers dont want one big wire of 0.2mm diameter, they want two wires a smaller diameter stuck together to make one of 0.2mm. So on machine 1 instead of making 200kg of 0.2mm it needs to make 150kg of 0.07mm + 50kg of 0.05mm or whatever is needed. The product code I referred to in the last post (A,B,C) can be thought of as wire diameter. What I would like is a method of excel deciding which bobines can be grouped and which cannot. In order to be grouped they must have the same diameter AND be sequential on the list. If they cant be grouped, too bad for us, we make it on smaller bobines. If one customer wants 600kg in any case he will be forced to take two bobines, one of 400kg and one of 200kg. If however the next customer wants the same diameter for 100kg we can combine that with the second bobine of 200kg we will make for the first customer so the factory would make 400kg and a 300kg bobine on machine 1. If however one customer wanted 390kg and another 50kg we do not want to make one 400kg and one of 40kg as the first customer can have his 390 but the second will have a 10 with a 40 joined to it not very useful in cables. Third case is if the first customer wants more than 420kg and the second 50kg we do not want to combine the orders as this would create a very small second bobine of 70kg. In this case we need do something special to make a 420kg and a 50kg costly and time consuming but thats life. So the logic is - test if diameters (A,B,C etc) are the same from cols A and C - if false return the original bobine weight - if true add the bobine weights and test if =<400, o if true add the next bobine weight, retest to see if the cumulative wght =<400 and this continues until it hits the limit ļ§ when it hits the limit it writes the bobine weights to be produced opposite the last item, o if false test to see if all the bobines are between 100 and 400kg. ļ§ if no more additions possible divides the bobines weights as required by the grouping and customers opposite the last item. ļ§ if can add more continue until it hits the limit then write the individual bobine weights opposite the last item Does that (quite long, maybe too long) description make it any easier? "Patrick Molloy" wrote: I can't get my head around this I'm afraid. We need an algorithm. That is, a sequence of logical steps ... You're very close to this project, so it woudl help maybe if you took a step back and started as if we're total beginners - which we are ;) thanks "LiAD" wrote: Morning, I have a problem which I have tried to post on the functions forum but no answer. Its difficult to explain so Im not sure if people are understanding what Im after. In order to make copper wire it is wound onto bobines which can support a maximum weight of 400kg. The machine that makes the bobines is very fast so if it can avoided its better not to make bobines smaller than 100kg, however if we must we can. If a customer needs 600kg of wire they will get a bobine of 400kg and another of 200kg. To make it more difficult each product can be based on several sub compositions. First of all Ill explain it for the products made from single components if thats not understood then the full solution will be completely lost. Single components- What I would like is a formula/macro that can look through the data and group the products that are the same. In col A of the sheet named (Bobines) I have my product list (A,B,C) in col B I have the individual weights of the bobines required. In col C,D,E¦¦ I would like to generate the results the individual weights of the bobines to be produced. The sequence is match the products, sum the weights then if: - the sum is less than 400kg continue to add weights until the limit of 400 is reached - the sum (or a component) is over 400kg continue adding until the orders can be split into 100kg< bobine weight < 400kg. - if no sum is possible just write the weight of the bobine as per the original customer order. - Where weights are being added to the next bobine just write nothing (blank or ) as a result - write the cumulative weights in the cell adjacent to the last bobine that was added (Just for ref these bobines are not actually sold then are then fed into other processes so they will be resplit into the original orders further down the line after additional work). Results Product Customer Wt Bob 1 Bob 2 B 390 390 B 90 90 C 400 400 C 90 90 A 420 --- A 90 400 110 D 600 ---- D 90 400 290 B 50 ---- B 100 150 Multiple components The added difficulty here is that the formula/code needs to look in several places to produce the result as it can match with the product in col A OR col C AND continue as long vertically as it wants until it reaches the limits of 100kg < xx < 400kg. In total there are four positions in which to match between consecutive rows (A-A, A-C, C-A and C-C). Results A 100 --- --- --- --- A 100 --- --- --- --- A 50 A 75 --- --- A 425 B 150 B 55 --- --- --- --- B 175 C 65 B 380 --- --- B 55 C 75 B 55 --- --- C 125 D 85 C 200 D 85 B 20 A 50 B 20 A 50 In both cases only items that are adjacent on the list should be grouped irrespective of weights, (hence why the last group of A&Bs are not grouped with the first lot). It is the second case of multiple components that I need to get to as a working result. Is this iterative procedure possible? I guess a macro is the easiest way to go otherwise itll be a never ending list of IF formulas. Thanks LiAD |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
I didn't know if you could combine multiple rows. I removed one section of
the code. Can I make the code for the multicomponent as the single components? I supplied a 2nd macro below which just moves the output to different column so I can make the two macro the same macro. Sub CombineOrders() With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row PreviousOrder = 0 For RowCount = 2 To LastRow Order = .Range("B" & RowCount) Order = Order + PreviousOrder If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then 'product matches NextOrder = .Range("B" & (RowCount + 1)) If Order <= 400 Then If Order + NextOrder <= 400 Then PreviousOrder = Order Else .Range("C" & RowCount) = Order End If Else PreviousOrder = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Next RowCount End With End Sub -------------------------------------------------------------------------------------------- same code with diffeent outputs Sub CombineOrders() With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row PreviousOrder = 0 For RowCount = 2 To LastRow Product = .Range("A" & RowCount) NextProduct = .Range("A" & (RowCount + 1)) Order = .Range("B" & RowCount) Order = Order + PreviousOrder If Product = NextProduct Then 'product matches NextOrder = .Range("B" & (RowCount + 1)) If Order <= 400 Then If Order + NextOrder <= 400 Then PreviousOrder = Order Else .Range("E" & RowCount) = Product .Range("F" & RowCount) = Order End If Else PreviousOrder = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range("E" & RowCount) = Product .Range("F" & RowCount) = Order Else .Range("E" & RowCount) = Product .Range("F" & RowCount) = 400 .Range("G" & RowCount) = Product .Range("H" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Next RowCount End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
Try this. Should work in both cases
Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D For Item = 1 To 2 If .Range("E" & RowCount) = "" Then NewCol = "E" Else NewCol = "G" End If If Item = 1 Then NewProduct = .Range("A" & RowCount) NewOrder = .Range("B" & RowCount) Else NewProduct = .Range("C" & RowCount) NewOrder = .Range("D" & RowCount) End If If NewProduct < "" Then 'see if new product matches one of products on bobines If NewProduct = OldProduct(1) Then OldItem = 1 Else If NewProduct = OldProduct(2) Then OldItem = 2 Else 'does not match, see which bobine is empty If OldProduct(1) = "" Then OldItem = 1 OldProduct(OldItem) = NewProduct Else If OldProduct(2) = "" Then OldItem = 2 OldProduct(OldItem) = NewProduct Else '2nd bobine should be empty, if not error Stop End If End If End If End If Order = OldOrder(OldItem) + NewOrder Found = False For CompareItem = (Item + 1) To 4 'don't compare against itself If NextProduct(Item) = NextProduct(CompareItem) Then NextItem = CompareItem Found = True Exit For End If Next CompareItem If Found = True Then 'product matches Quant = NextOrder(NextItem) If Order <= 400 Then If Order + Quant <= 400 Then OldOrder(OldItem) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If Else OldOrder(OldItem) = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = 400 .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 End If OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If End If Next Item Next RowCount End With End Sub "LiAD" wrote: Hi, Thanks a lot for your help. Relief to find that maybe a way is possible! One thing I've noticed is that this code stops after 2 rows. So the inputs and result for the following is A 50 A 50 100 A 200 200 whereas the desired output is A 50 A 50 A 200 300 I guess its better to know now rather than later. Thanks a lot for your help again LiAD "Joel" wrote: the code below give the first table results. the code is checking only column A to see if products match. I have to look at the 2nd table. will get back to you later Sub CombineOrders() With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row PreviousOrder = 0 For RowCount = 2 To LastRow Order = .Range("B" & RowCount) Order = Order + PreviousOrder If PreviousOrder = 0 Then If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then 'product matches NextOrder = .Range("B" & (RowCount + 1)) If Order <= 400 Then If Order + NextOrder <= 400 Then PreviousOrder = Order Else .Range("C" & RowCount) = Order End If Else PreviousOrder = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Else 'previous order doesn't match don't not combine 3 rows If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Next RowCount End With End Sub "LiAD" wrote: Yeah I know Im too close. Ill try more simply. Copper wire is made in certain diameters, 0.2mm, 0.3mm, 0.4mm etc. In order to transport the wire is wound onto bobines. Each bobine size has a maximum weight that it can support 400kg in this case. Imagine a two stage process where u make the wire xx diameter then u send it to another machine to get the plastic put around the outside. The output from the plastic wrapping machine will also be wound onto bobines. Customer A would like 200kg, B 100kg, C 50kg all of exactly the same wire, 0.3mm with a red plastic coat. On machine 1 to save time, bobines, space etc we will make one big bobine of 350kg of 0.3mm wire to send the wrapping machine. The wire will be wrapped in red plastic and wound onto three separate bobines (200,100,50kg). If we dont combine the orders on the first machine we need more bobines, more stopping/starting of the machine, more set-ups, more time etc etc. By combining the customer gets what they need and we reduce our timescale. Life not being so easy they dont all want the same thing of course. They want different diameters, colours and compositions. Compositions???? Some customers dont want one big wire of 0.2mm diameter, they want two wires a smaller diameter stuck together to make one of 0.2mm. So on machine 1 instead of making 200kg of 0.2mm it needs to make 150kg of 0.07mm + 50kg of 0.05mm or whatever is needed. The product code I referred to in the last post (A,B,C) can be thought of as wire diameter. What I would like is a method of excel deciding which bobines can be grouped and which cannot. In order to be grouped they must have the same diameter AND be sequential on the list. If they cant be grouped, too bad for us, we make it on smaller bobines. If one customer wants 600kg in any case he will be forced to take two bobines, one of 400kg and one of 200kg. If however the next customer wants the same diameter for 100kg we can combine that with the second bobine of 200kg we will make for the first customer so the factory would make 400kg and a 300kg bobine on machine 1. If however one customer wanted 390kg and another 50kg we do not want to make one 400kg and one of 40kg as the first customer can have his 390 but the second will have a 10 with a 40 joined to it not very useful in cables. Third case is if the first customer wants more than 420kg and the second 50kg we do not want to combine the orders as this would create a very small second bobine of 70kg. In this case we need do something special to make a 420kg and a 50kg costly and time consuming but thats life. So the logic is - test if diameters (A,B,C etc) are the same from cols A and C - if false return the original bobine weight - if true add the bobine weights and test if =<400, o if true add the next bobine weight, retest to see if the cumulative wght =<400 and this continues until it hits the limit ļ§ when it hits the limit it writes the bobine weights to be produced opposite the last item, o if false test to see if all the bobines are between 100 and 400kg. ļ§ if no more additions possible divides the bobines weights as required by the grouping and customers opposite the last item. ļ§ if can add more continue until it hits the limit then write the individual bobine weights opposite the last item Does that (quite long, maybe too long) description make it any easier? "Patrick Molloy" wrote: I can't get my head around this I'm afraid. We need an algorithm. That is, a sequence of logical steps ... You're very close to this project, so it woudl help maybe if you took a step back and started as if we're total beginners - which we are ;) thanks "LiAD" wrote: Morning, I have a problem which I have tried to post on the functions forum but no answer. Its difficult to explain so Im not sure if people are understanding what Im after. In order to make copper wire it is wound onto bobines which can support a maximum weight of 400kg. The machine that makes the bobines is very fast so if it can avoided its better not to make bobines smaller than 100kg, however if we must we can. If a customer needs 600kg of wire they will get a bobine of 400kg and another of 200kg. To make it more difficult each product can be based on several sub compositions. First of all Ill explain it for the products made from single components if thats not understood then the full solution will be completely lost. Single components- What I would like is a formula/macro that can look through the data and group the products that are the same. In col A of the sheet named (Bobines) I have my product list (A,B,C) in col B I have the individual weights of the bobines required. In col C,D,E¦¦ I would like to generate the results the individual weights of the bobines to be produced. The sequence is match the products, sum the weights then if: - the sum is less than 400kg continue to add weights until the limit of 400 is reached - the sum (or a component) is over 400kg continue adding until the orders can be split into 100kg< bobine weight < 400kg. - if no sum is possible just write the weight of the bobine as per the original customer order. - Where weights are being added to the next bobine just write nothing (blank or ) as a result - write the cumulative weights in the cell adjacent to the last bobine that was added (Just for ref these bobines are not actually sold then are then fed into other processes so they will be resplit into the original orders further down the line after additional work). Results Product Customer Wt Bob 1 Bob 2 B 390 390 B 90 90 C 400 400 C 90 90 A 420 --- A 90 400 110 D 600 ---- D 90 400 290 B 50 ---- B 100 150 Multiple components The added difficulty here is that the formula/code needs to look in several places to produce the result as it can match with the product in col A OR col C AND continue as long vertically as it wants until it reaches the limits of 100kg < xx < 400kg. In total there are four positions in which to match between consecutive rows (A-A, A-C, C-A and C-C). Results A 100 --- --- --- --- A 100 --- --- --- --- A 50 A 75 --- --- A 425 B 150 B 55 --- --- --- --- B 175 C 65 B 380 --- --- B 55 C 75 B 55 --- --- C 125 D 85 C 200 D 85 B 20 A 50 B 20 A 50 In both cases only items that are adjacent on the list should be grouped irrespective of weights, (hence why the last group of A&Bs are not grouped with the first lot). It is the second case of multiple components that I need to get to as a working result. Is this iterative procedure possible? I guess a macro is the easiest way to go otherwise itll be a never ending list of IF formulas. Thanks LiAD |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
Hot stuff. Love it!
Its super close but there are just a few little things Ive noticed. Grouping doesnt seem to work in cases such as the following Inputs ------ Results given ------- A 150 A 3200 A 150 A 250 A 400 A 3050 In this case the formula should give two only A's, 400 and 3200. If I try the same inputs but in a different order I get a different result Inputs ------------- Results given--------- A 150 A 250 A 3200 A 400 A 400 A 2800 - Is it possible to have the same outputs in the two cases above? - Is it possible to assure that the last output is always a multiple of 400, so for example if we replaced the 3200 with 3300 the outputs would be 400,100,3200? - Is there a way of ensuring the position of the results is constant? In the example below it puts the last result for C one space to the right, is it possible to line up this result with the others? A 50 A 50 A 200 A 300 A 200 C 100 A 200 C 100 B 300 B 300 B 175 B 300 B 175 B 55 C 500 B 355 C 125 C 400 C 225 The last C in the bottom right should be below the C four cells above (col G) rather than the 100 (col H). Thanks a million for your help LiAD ------------------------------------------- "Joel" wrote: Try this. Should work in both cases Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D For Item = 1 To 2 If .Range("E" & RowCount) = "" Then NewCol = "E" Else NewCol = "G" End If If Item = 1 Then NewProduct = .Range("A" & RowCount) NewOrder = .Range("B" & RowCount) Else NewProduct = .Range("C" & RowCount) NewOrder = .Range("D" & RowCount) End If If NewProduct < "" Then 'see if new product matches one of products on bobines If NewProduct = OldProduct(1) Then OldItem = 1 Else If NewProduct = OldProduct(2) Then OldItem = 2 Else 'does not match, see which bobine is empty If OldProduct(1) = "" Then OldItem = 1 OldProduct(OldItem) = NewProduct Else If OldProduct(2) = "" Then OldItem = 2 OldProduct(OldItem) = NewProduct Else '2nd bobine should be empty, if not error Stop End If End If End If End If Order = OldOrder(OldItem) + NewOrder Found = False For CompareItem = (Item + 1) To 4 'don't compare against itself If NextProduct(Item) = NextProduct(CompareItem) Then NextItem = CompareItem Found = True Exit For End If Next CompareItem If Found = True Then 'product matches Quant = NextOrder(NextItem) If Order <= 400 Then If Order + Quant <= 400 Then OldOrder(OldItem) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If Else OldOrder(OldItem) = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = 400 .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 End If OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If End If Next Item Next RowCount End With End Sub "LiAD" wrote: Hi, Thanks a lot for your help. Relief to find that maybe a way is possible! One thing I've noticed is that this code stops after 2 rows. So the inputs and result for the following is A 50 A 50 100 A 200 200 whereas the desired output is A 50 A 50 A 200 300 I guess its better to know now rather than later. Thanks a lot for your help again LiAD "Joel" wrote: the code below give the first table results. the code is checking only column A to see if products match. I have to look at the 2nd table. will get back to you later Sub CombineOrders() With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row PreviousOrder = 0 For RowCount = 2 To LastRow Order = .Range("B" & RowCount) Order = Order + PreviousOrder If PreviousOrder = 0 Then If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then 'product matches NextOrder = .Range("B" & (RowCount + 1)) If Order <= 400 Then If Order + NextOrder <= 400 Then PreviousOrder = Order Else .Range("C" & RowCount) = Order End If Else PreviousOrder = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Else 'previous order doesn't match don't not combine 3 rows If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Next RowCount End With End Sub "LiAD" wrote: Yeah I know Im too close. Ill try more simply. Copper wire is made in certain diameters, 0.2mm, 0.3mm, 0.4mm etc. In order to transport the wire is wound onto bobines. Each bobine size has a maximum weight that it can support 400kg in this case. Imagine a two stage process where u make the wire xx diameter then u send it to another machine to get the plastic put around the outside. The output from the plastic wrapping machine will also be wound onto bobines. Customer A would like 200kg, B 100kg, C 50kg all of exactly the same wire, 0.3mm with a red plastic coat. On machine 1 to save time, bobines, space etc we will make one big bobine of 350kg of 0.3mm wire to send the wrapping machine. The wire will be wrapped in red plastic and wound onto three separate bobines (200,100,50kg). If we dont combine the orders on the first machine we need more bobines, more stopping/starting of the machine, more set-ups, more time etc etc. By combining the customer gets what they need and we reduce our timescale. Life not being so easy they dont all want the same thing of course. They want different diameters, colours and compositions. Compositions???? Some customers dont want one big wire of 0.2mm diameter, they want two wires a smaller diameter stuck together to make one of 0.2mm. So on machine 1 instead of making 200kg of 0.2mm it needs to make 150kg of 0.07mm + 50kg of 0.05mm or whatever is needed. The product code I referred to in the last post (A,B,C) can be thought of as wire diameter. What I would like is a method of excel deciding which bobines can be grouped and which cannot. In order to be grouped they must have the same diameter AND be sequential on the list. If they cant be grouped, too bad for us, we make it on smaller bobines. If one customer wants 600kg in any case he will be forced to take two bobines, one of 400kg and one of 200kg. If however the next customer wants the same diameter for 100kg we can combine that with the second bobine of 200kg we will make for the first customer so the factory would make 400kg and a 300kg bobine on machine 1. If however one customer wanted 390kg and another 50kg we do not want to make one 400kg and one of 40kg as the first customer can have his 390 but the second will have a 10 with a 40 joined to it not very useful in cables. Third case is if the first customer wants more than 420kg and the second 50kg we do not want to combine the orders as this would create a very small second bobine of 70kg. In this case we need do something special to make a 420kg and a 50kg costly and time consuming but thats life. So the logic is - test if diameters (A,B,C etc) are the same from cols A and C - if false return the original bobine weight - if true add the bobine weights and test if =<400, o if true add the next bobine weight, retest to see if the cumulative wght =<400 and this continues until it hits the limit ļ§ when it hits the limit it writes the bobine weights to be produced opposite the last item, o if false test to see if all the bobines are between 100 and 400kg. ļ§ if no more additions possible divides the bobines weights as required by the grouping and customers opposite the last item. ļ§ if can add more continue until it hits the limit then write the individual bobine weights opposite the last item Does that (quite long, maybe too long) description make it any easier? "Patrick Molloy" wrote: I can't get my head around this I'm afraid. We need an algorithm. That is, a sequence of logical steps ... You're very close to this project, so it woudl help maybe if you took a step back and started as if we're total beginners - which we are ;) thanks "LiAD" wrote: Morning, I have a problem which I have tried to post on the functions forum but no answer. Its difficult to explain so Im not sure if people are understanding what Im after. In order to make copper wire it is wound onto bobines which can support a maximum weight of 400kg. The machine that makes the bobines is very fast so |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
I lited all the changes I made and then the new code
Changes to fix columns from .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 to .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - 400 Change to fix combining orders from If Order <= 400 Then If Order + Quant <= 400 Then OldOrder(OldItem) = Order Else to If Order + Quant <= 400 Then If Order <= 400 Then OldOrder(OldItem) = Order Else To make last row multiple of 400 I used two bobines instead of 3 (100, 3600). Also see if you like the result in this case A 150 A 250 A 3200 A 500 from .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = 400 .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 to Remainder = Order Mod 400 .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Remainder .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder New Code Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D For Item = 1 To 2 If .Range("E" & RowCount) = "" Then NewCol = "E" Else NewCol = "G" End If If Item = 1 Then NewProduct = .Range("A" & RowCount) NewOrder = .Range("B" & RowCount) Else NewProduct = .Range("C" & RowCount) NewOrder = .Range("D" & RowCount) End If If NewProduct < "" Then 'see if new product matches one of products on bobines If NewProduct = OldProduct(1) Then OldItem = 1 Else If NewProduct = OldProduct(2) Then OldItem = 2 Else 'does not match, see which bobine is empty If OldProduct(1) = "" Then OldItem = 1 OldProduct(OldItem) = NewProduct Else If OldProduct(2) = "" Then OldItem = 2 OldProduct(OldItem) = NewProduct Else '2nd bobine should be empty, if not error Stop End If End If End If End If Order = OldOrder(OldItem) + NewOrder Found = False For CompareItem = (Item + 1) To 4 'don't compare against itself If NextProduct(Item) = NextProduct(CompareItem) Then NextItem = CompareItem Found = True Exit For End If Next CompareItem If Found = True Then 'product matches Quant = NextOrder(NextItem) If Order + Quant <= 400 Then If Order <= 400 Then OldOrder(OldItem) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If Else OldOrder(OldItem) = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order Else Remainder = Order Mod 400 .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Remainder .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder End If OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If End If Next Item Next RowCount End With End Sub "LiAD" wrote: Hot stuff. Love it! Its super close but there are just a few little things Ive noticed. Grouping doesnt seem to work in cases such as the following Inputs ------ Results given ------- A 150 A 3200 A 150 A 250 A 400 A 3050 In this case the formula should give two only A's, 400 and 3200. If I try the same inputs but in a different order I get a different result Inputs ------------- Results given--------- A 150 A 250 A 3200 A 400 A 400 A 2800 - Is it possible to have the same outputs in the two cases above? - Is it possible to assure that the last output is always a multiple of 400, so for example if we replaced the 3200 with 3300 the outputs would be 400,100,3200? - Is there a way of ensuring the position of the results is constant? In the example below it puts the last result for C one space to the right, is it possible to line up this result with the others? A 50 A 50 A 200 A 300 A 200 C 100 A 200 C 100 B 300 B 300 B 175 B 300 B 175 B 55 C 500 B 355 C 125 C 400 C 225 The last C in the bottom right should be below the C four cells above (col G) rather than the 100 (col H). Thanks a million for your help LiAD ------------------------------------------- "Joel" wrote: Try this. Should work in both cases Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D For Item = 1 To 2 If .Range("E" & RowCount) = "" Then NewCol = "E" Else NewCol = "G" End If If Item = 1 Then NewProduct = .Range("A" & RowCount) NewOrder = .Range("B" & RowCount) Else NewProduct = .Range("C" & RowCount) NewOrder = .Range("D" & RowCount) End If If NewProduct < "" Then 'see if new product matches one of products on bobines If NewProduct = OldProduct(1) Then OldItem = 1 Else If NewProduct = OldProduct(2) Then OldItem = 2 Else 'does not match, see which bobine is empty If OldProduct(1) = "" Then OldItem = 1 OldProduct(OldItem) = NewProduct Else If OldProduct(2) = "" Then OldItem = 2 OldProduct(OldItem) = NewProduct Else '2nd bobine should be empty, if not error Stop End If End If End If End If Order = OldOrder(OldItem) + NewOrder Found = False For CompareItem = (Item + 1) To 4 'don't compare against itself If NextProduct(Item) = NextProduct(CompareItem) Then NextItem = CompareItem Found = True Exit For End If Next CompareItem If Found = True Then 'product matches Quant = NextOrder(NextItem) If Order <= 400 Then If Order + Quant <= 400 Then OldOrder(OldItem) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If Else OldOrder(OldItem) = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = 400 .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 End If OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If End If Next Item Next RowCount End With End Sub "LiAD" wrote: Hi, Thanks a lot for your help. Relief to find that maybe a way is possible! One thing I've noticed is that this code stops after 2 rows. So the inputs and result for the following is A 50 A 50 100 A 200 200 whereas the desired output is A 50 A 50 A 200 300 I guess its better to know now rather than later. Thanks a lot for your help again LiAD "Joel" wrote: the code below give the first table results. the code is checking only column A to see if products match. I have to look at the 2nd table. will get back to you later Sub CombineOrders() With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row PreviousOrder = 0 For RowCount = 2 To LastRow Order = .Range("B" & RowCount) Order = Order + PreviousOrder If PreviousOrder = 0 Then If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then 'product matches NextOrder = .Range("B" & (RowCount + 1)) If Order <= 400 Then If Order + NextOrder <= 400 Then PreviousOrder = Order Else .Range("C" & RowCount) = Order End If Else PreviousOrder = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Else 'previous order doesn't match don't not combine 3 rows If Order <= 400 Then .Range("C" & RowCount) = Order Else .Range("C" & RowCount) = 400 .Range("D" & RowCount) = Order - 400 End If PreviousOrder = 0 End If Next RowCount End With End Sub "LiAD" wrote: Yeah I know Im too close. Ill try more simply. Copper wire is made in certain diameters, 0.2mm, 0.3mm, 0.4mm etc. In order to transport the wire is wound onto bobines. Each bobine size has a maximum weight that it can support 400kg in this case. Imagine a two stage process where u make the wire xx diameter then u send it to another machine to get the plastic put around the outside. The output from the plastic wrapping machine will also be wound onto bobines. Customer A would like 200kg, B 100kg, C 50kg all of exactly the same wire, 0.3mm with a red plastic coat. On machine 1 to save time, bobines, space etc we will make one big bobine of 350kg of 0.3mm wire to send the wrapping machine. The wire will be wrapped in red plastic and wound onto three separate bobines (200,100,50kg). If we dont combine the orders on the first machine we need more bobines, more stopping/starting of the machine, more set-ups, more time etc etc. By combining the customer gets what they need and we reduce our timescale. Life not being so easy they dont all want the same thing of course. They want different diameters, colours and compositions. Compositions???? Some customers dont want one big wire of 0.2mm diameter, they want two wires a smaller diameter stuck together to make one of 0.2mm. So on machine 1 instead of making 200kg of 0.2mm it needs to make 150kg of 0.07mm + 50kg of 0.05mm or whatever is needed. The product code I referred to in the last post (A,B,C) can be thought of as wire diameter. What I would like is a method of excel deciding which bobines can be grouped and which cannot. In order to be grouped they must have the same diameter AND be sequential on the list. If they cant be grouped, too bad for us, we make it on smaller bobines. If one customer wants 600kg in any case he will be forced to take two bobines, one of 400kg and one of 200kg. If however the next customer wants the same diameter for 100kg we can combine that with the second bobine of 200kg we will make for the first customer so the factory would make 400kg and a 300kg bobine on machine 1. If however one customer wanted 390kg and another 50kg we do not want to make one 400kg and one of 40kg as the first customer can have his 390 but the second will have a 10 with a 40 joined to it not very useful in cables. Third case is if the first customer wants more than 420kg and the second 50kg we do not want to combine the orders as this would create a very small second bobine of 70kg. In this case we need do something special to make a 420kg and a 50kg costly and time consuming but thats life. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
Closer than close!!
Thanks a lot for your help and sorry to seek another mod, i'm sure it took a while to get to here. Is it possible to check for one last problem - disappearing results. When I have four inputs, both of which will have two ouputs as the weights are over 400 i loose one of results. Example below ----------------Results-------------- A 100 D 2100 A 2000 D 2000 A 100 D 100 D 4000 (2000 of A missing) If i introduce a third product (replace the first D with a B), I get the same result product A missing 2000. If I then make it the worst case, four different products all overthe limit the results is two items missing quantities. --------------Results-------------- C 5000 B 2100 C 200 B 100 B 2000 A 2000 D 2000 A 0 D 0 D 2000 If the first two columns and are kept for the bobines that are <=400 and the last two are for the multiples of 400 (whole full bobines only) maybe this is the easiest way to ensure that all will be counted? I have to send u some beer over for your efforts! LiAD "Joel" wrote: I lited all the changes I made and then the new code Changes to fix columns from .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 to .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - 400 Change to fix combining orders from If Order <= 400 Then If Order + Quant <= 400 Then OldOrder(OldItem) = Order Else to If Order + Quant <= 400 Then If Order <= 400 Then OldOrder(OldItem) = Order Else To make last row multiple of 400 I used two bobines instead of 3 (100, 3600). Also see if you like the result in this case A 150 A 250 A 3200 A 500 from .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = 400 .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 to Remainder = Order Mod 400 .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Remainder .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder New Code Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D For Item = 1 To 2 If .Range("E" & RowCount) = "" Then NewCol = "E" Else NewCol = "G" End If If Item = 1 Then NewProduct = .Range("A" & RowCount) NewOrder = .Range("B" & RowCount) Else NewProduct = .Range("C" & RowCount) NewOrder = .Range("D" & RowCount) End If If NewProduct < "" Then 'see if new product matches one of products on bobines If NewProduct = OldProduct(1) Then OldItem = 1 Else If NewProduct = OldProduct(2) Then OldItem = 2 Else 'does not match, see which bobine is empty If OldProduct(1) = "" Then OldItem = 1 OldProduct(OldItem) = NewProduct Else If OldProduct(2) = "" Then OldItem = 2 OldProduct(OldItem) = NewProduct Else '2nd bobine should be empty, if not error Stop End If End If End If End If Order = OldOrder(OldItem) + NewOrder Found = False For CompareItem = (Item + 1) To 4 'don't compare against itself If NextProduct(Item) = NextProduct(CompareItem) Then NextItem = CompareItem Found = True Exit For End If Next CompareItem If Found = True Then 'product matches Quant = NextOrder(NextItem) If Order + Quant <= 400 Then If Order <= 400 Then OldOrder(OldItem) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If Else OldOrder(OldItem) = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order Else Remainder = Order Mod 400 .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Remainder .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder End If OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If End If Next Item Next RowCount End With End Sub "LiAD" wrote: Hot stuff. Love it! Its super close but there are just a few little things Ive noticed. Grouping doesnt seem to work in cases such as the following Inputs ------ Results given ------- A 150 A 3200 A 150 A 250 A 400 A 3050 In this case the formula should give two only A's, 400 and 3200. If I try the same inputs but in a different order I get a different result Inputs ------------- Results given--------- A 150 A 250 A 3200 A 400 A 400 A 2800 - Is it possible to have the same outputs in the two cases above? - Is it possible to assure that the last output is always a multiple of 400, so for example if we replaced the 3200 with 3300 the outputs would be 400,100,3200? - Is there a way of ensuring the position of the results is constant? In the example below it puts the last result for C one space to the right, is it possible to line up this result with the others? A 50 A 50 A 200 A 300 A 200 C 100 A 200 C 100 B 300 B 300 B 175 B 300 B 175 B 55 C 500 B 355 C 125 C 400 C 225 The last C in the bottom right should be below the C four cells above (col G) rather than the 100 (col H). Thanks a million for your help LiAD ------------------------------------------- "Joel" wrote: Try this. Should work in both cases Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D For Item = 1 To 2 If .Range("E" & RowCount) = "" Then NewCol = "E" Else NewCol = "G" End If If Item = 1 Then NewProduct = .Range("A" & RowCount) NewOrder = .Range("B" & RowCount) Else NewProduct = .Range("C" & RowCount) NewOrder = .Range("D" & RowCount) End If If NewProduct < "" Then 'see if new product matches one of products on bobines If NewProduct = OldProduct(1) Then OldItem = 1 Else If NewProduct = OldProduct(2) Then OldItem = 2 Else 'does not match, see which bobine is empty If OldProduct(1) = "" Then OldItem = 1 OldProduct(OldItem) = NewProduct Else If OldProduct(2) = "" Then OldItem = 2 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
the code worked fine. i just was over-writing the data column G
from If .Range("E" & RowCount) = "" Then NewCol = "E" Else NewCol = "G" End If to If .Range("E" & RowCount) = "" Then NewCol = "E" Else If .Range("G" & RowCount) = "" Then NewCol = "G" Else NewCol = "I" End If End If update code Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D For Item = 1 To 2 If .Range("E" & RowCount) = "" Then NewCol = "E" Else If .Range("G" & RowCount) = "" Then NewCol = "G" Else NewCol = "I" End If End If If Item = 1 Then NewProduct = .Range("A" & RowCount) NewOrder = .Range("B" & RowCount) Else NewProduct = .Range("C" & RowCount) NewOrder = .Range("D" & RowCount) End If If NewProduct < "" Then 'see if new product matches one of products on bobines If NewProduct = OldProduct(1) Then OldItem = 1 Else If NewProduct = OldProduct(2) Then OldItem = 2 Else 'does not match, see which bobine is empty If OldProduct(1) = "" Then OldItem = 1 OldProduct(OldItem) = NewProduct Else If OldProduct(2) = "" Then OldItem = 2 OldProduct(OldItem) = NewProduct Else '2nd bobine should be empty, if not error Stop End If End If End If End If Order = OldOrder(OldItem) + NewOrder Found = False For CompareItem = (Item + 1) To 4 'don't compare against itself If NextProduct(Item) = NextProduct(CompareItem) Then NextItem = CompareItem Found = True Exit For End If Next CompareItem If Found = True Then 'product matches Quant = NextOrder(NextItem) If Order + Quant <= 400 Then If Order <= 400 Then OldOrder(OldItem) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If Else OldOrder(OldItem) = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order Else Remainder = Order Mod 400 .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Remainder .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder End If OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If End If Next Item Next RowCount End With End Sub "LiAD" wrote: Closer than close!! Thanks a lot for your help and sorry to seek another mod, i'm sure it took a while to get to here. Is it possible to check for one last problem - disappearing results. When I have four inputs, both of which will have two ouputs as the weights are over 400 i loose one of results. Example below ----------------Results-------------- A 100 D 2100 A 2000 D 2000 A 100 D 100 D 4000 (2000 of A missing) If i introduce a third product (replace the first D with a B), I get the same result product A missing 2000. If I then make it the worst case, four different products all overthe limit the results is two items missing quantities. --------------Results-------------- C 5000 B 2100 C 200 B 100 B 2000 A 2000 D 2000 A 0 D 0 D 2000 If the first two columns and are kept for the bobines that are <=400 and the last two are for the multiples of 400 (whole full bobines only) maybe this is the easiest way to ensure that all will be counted? I have to send u some beer over for your efforts! LiAD "Joel" wrote: I lited all the changes I made and then the new code Changes to fix columns from .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 to .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - 400 Change to fix combining orders from If Order <= 400 Then If Order + Quant <= 400 Then OldOrder(OldItem) = Order Else to If Order + Quant <= 400 Then If Order <= 400 Then OldOrder(OldItem) = Order Else To make last row multiple of 400 I used two bobines instead of 3 (100, 3600). Also see if you like the result in this case A 150 A 250 A 3200 A 500 from .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = 400 .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 to Remainder = Order Mod 400 .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Remainder .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder New Code Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D For Item = 1 To 2 If .Range("E" & RowCount) = "" Then NewCol = "E" Else NewCol = "G" End If If Item = 1 Then NewProduct = .Range("A" & RowCount) NewOrder = .Range("B" & RowCount) Else NewProduct = .Range("C" & RowCount) NewOrder = .Range("D" & RowCount) End If If NewProduct < "" Then 'see if new product matches one of products on bobines If NewProduct = OldProduct(1) Then OldItem = 1 Else If NewProduct = OldProduct(2) Then OldItem = 2 Else 'does not match, see which bobine is empty If OldProduct(1) = "" Then OldItem = 1 OldProduct(OldItem) = NewProduct Else If OldProduct(2) = "" Then OldItem = 2 OldProduct(OldItem) = NewProduct Else '2nd bobine should be empty, if not error Stop End If End If End If End If Order = OldOrder(OldItem) + NewOrder Found = False For CompareItem = (Item + 1) To 4 'don't compare against itself If NextProduct(Item) = NextProduct(CompareItem) Then NextItem = CompareItem Found = True Exit For End If Next CompareItem If Found = True Then 'product matches Quant = NextOrder(NextItem) If Order + Quant <= 400 Then If Order <= 400 Then OldOrder(OldItem) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If Else OldOrder(OldItem) = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order Else Remainder = Order Mod 400 .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Remainder .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder End If OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If End If Next Item Next RowCount End With End Sub "LiAD" wrote: Hot stuff. Love it! Its super close but there are just a few little things Ive noticed. Grouping doesnt seem to work in cases such as the following Inputs ------ Results given ------- A 150 A 3200 A 150 A 250 A 400 A 3050 In this case the formula should give two only A's, 400 and 3200. If I try the same inputs but in a different order I get a different result Inputs ------------- Results given--------- A 150 A 250 A 3200 A 400 A 400 A 2800 - Is it possible to have the same outputs in the two cases above? - Is it possible to assure that the last output is always a multiple of 400, so for example if we replaced the 3200 with 3300 the outputs would be 400,100,3200? - Is there a way of ensuring the position of the results is constant? In the example below it puts the last result for C one space to the right, is it possible to line up this result with the others? A 50 A 50 A 200 A 300 A 200 C 100 A 200 C 100 B 300 B 300 B 175 B 300 B 175 B 55 C 500 B 355 C 125 C 400 C 225 The last C in the bottom right should be below the C four cells above (col G) rather than the 100 (col H). Thanks a million for your help LiAD ------------------------------------------- "Joel" wrote: Try this. Should work in both cases Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated sum
Looks like the money shot right there!
Thanks a lot "Joel" wrote: the code worked fine. i just was over-writing the data column G from If .Range("E" & RowCount) = "" Then NewCol = "E" Else NewCol = "G" End If to If .Range("E" & RowCount) = "" Then NewCol = "E" Else If .Range("G" & RowCount) = "" Then NewCol = "G" Else NewCol = "I" End If End If update code Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D For Item = 1 To 2 If .Range("E" & RowCount) = "" Then NewCol = "E" Else If .Range("G" & RowCount) = "" Then NewCol = "G" Else NewCol = "I" End If End If If Item = 1 Then NewProduct = .Range("A" & RowCount) NewOrder = .Range("B" & RowCount) Else NewProduct = .Range("C" & RowCount) NewOrder = .Range("D" & RowCount) End If If NewProduct < "" Then 'see if new product matches one of products on bobines If NewProduct = OldProduct(1) Then OldItem = 1 Else If NewProduct = OldProduct(2) Then OldItem = 2 Else 'does not match, see which bobine is empty If OldProduct(1) = "" Then OldItem = 1 OldProduct(OldItem) = NewProduct Else If OldProduct(2) = "" Then OldItem = 2 OldProduct(OldItem) = NewProduct Else '2nd bobine should be empty, if not error Stop End If End If End If End If Order = OldOrder(OldItem) + NewOrder Found = False For CompareItem = (Item + 1) To 4 'don't compare against itself If NextProduct(Item) = NextProduct(CompareItem) Then NextItem = CompareItem Found = True Exit For End If Next CompareItem If Found = True Then 'product matches Quant = NextOrder(NextItem) If Order + Quant <= 400 Then If Order <= 400 Then OldOrder(OldItem) = Order Else .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If Else OldOrder(OldItem) = Order End If Else 'Product doesn't match put on bobbines If Order <= 400 Then .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Order Else Remainder = Order Mod 400 .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Remainder .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder End If OldProduct(OldItem) = "" OldOrder(OldItem) = 0 End If End If Next Item Next RowCount End With End Sub "LiAD" wrote: Closer than close!! Thanks a lot for your help and sorry to seek another mod, i'm sure it took a while to get to here. Is it possible to check for one last problem - disappearing results. When I have four inputs, both of which will have two ouputs as the weights are over 400 i loose one of results. Example below ----------------Results-------------- A 100 D 2100 A 2000 D 2000 A 100 D 100 D 4000 (2000 of A missing) If i introduce a third product (replace the first D with a B), I get the same result product A missing 2000. If I then make it the worst case, four different products all overthe limit the results is two items missing quantities. --------------Results-------------- C 5000 B 2100 C 200 B 100 B 2000 A 2000 D 2000 A 0 D 0 D 2000 If the first two columns and are kept for the bobines that are <=400 and the last two are for the multiples of 400 (whole full bobines only) maybe this is the easiest way to ensure that all will be counted? I have to send u some beer over for your efforts! LiAD "Joel" wrote: I lited all the changes I made and then the new code Changes to fix columns from .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 to .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - 400 Change to fix combining orders from If Order <= 400 Then If Order + Quant <= 400 Then OldOrder(OldItem) = Order Else to If Order + Quant <= 400 Then If Order <= 400 Then OldOrder(OldItem) = Order Else To make last row multiple of 400 I used two bobines instead of 3 (100, 3600). Also see if you like the result in this case A 150 A 250 A 3200 A 500 from .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = 400 .Range(NewCol & RowCount).Offset(0, 3) = NewProduct .Range(NewCol & RowCount).Offset(0, 4) = Order - 400 to Remainder = Order Mod 400 .Range(NewCol & RowCount) = NewProduct .Range(NewCol & RowCount).Offset(0, 1) = Remainder .Range(NewCol & RowCount).Offset(0, 2) = NewProduct .Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder New Code Sub CombineOrders() Dim OldProduct(1 To 2) Dim OldOrder(1 To 2) 'arrays fill with in the following order '1 = Col A and Col B data '2 = Col C and Col D '3 = Next Row Col A and Col B '4 = Next Row Col C and Col D Dim NextProduct(1 To 4) Dim NextOrder(1 To 4) With Sheets("bobines") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 1 To 4 NextProduct(i) = "" NextOrder(i) = 0 Next i For i = 1 To 2 OldOrder(i) = 0 OldProduct(i) = "" Next i For RowCount = 2 To LastRow NextProduct(1) = .Range("A" & RowCount) NextOrder(1) = .Range("B" & RowCount) NextProduct(2) = .Range("C" & RowCount) NextOrder(2) = .Range("D" & RowCount) NextProduct(3) = .Range("A" & (RowCount + 1)) NextOrder(3) = .Range("B" & (RowCount + 1)) NextProduct(4) = .Range("C" & (RowCount + 1)) NextOrder(4) = .Range("D" & (RowCount + 1)) 'loop twice, one for column A-B and then C-D For Item = 1 To 2 If .Range("E" & RowCount) = "" Then NewCol = "E" Else NewCol = "G" End If If Item = 1 Then NewProduct = .Range("A" & RowCount) NewOrder = .Range("B" & RowCount) Else NewProduct = .Range("C" & RowCount) NewOrder = .Range("D" & RowCount) End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated Look-Up | Excel Discussion (Misc queries) | |||
a little complicated | Excel Worksheet Functions | |||
Something perhaps a little complicated | Excel Discussion (Misc queries) | |||
Complicated | Excel Worksheet Functions | |||
It's getting a bit complicated | Excel Programming |