Logic Program for VBA.. may be hard

[jakesandelin]

I need some serious help. Noobie here at VBA but I am trying... so my goal is I have to regularly solve this problem where I have multiple items that I will sell to customers and I have to literally guess what items we gave them. So here is a little example with data to make this simple.

This is the data for everything sold this list can be 400 long or 20 long.

ID number, Amount, Length, Avg


980078
8
239.888
29.986


975184
7
213.32
30.47429


975337
19
579.593
30.50489


980079
4
122.169
30.54225


975338
19
580.61
30.55842


975336
19
581.463
30.60332


975688
19
583.268
30.69832


973334
19
583.366
30.70347


973333
19
584.186
30.74663


975671
19
584.514
30.76389



And, based off of the customer's information they give back to me this is what they received
Amount, Length, Average


64
9514.62
31.61003




So, basically I need to make the amount absolutely perfect. I'm allowed to break up the amount in the numbers above so that per say batch 975671 I can take just two pieces of the 19 that average at 30.76389 ft. But, I need to get the length almost perfect as well. Within .005 ft. This isn't always possible, but amazingly with an amazing amount of time I can usually make it work.. Is there a way I can automate this with code? I know this may be exceedingly hard and a lot to ask. But, I had no idea where to go. If you need clarification please just ask me.

Thanks guys!!!

[mdmackillop]

To restate: you need to take a number of parts form varying IDs to total 64 where the sum product of their average lengths will equal the stated length.
If so:
The average length of your 64 item is 148.6659, not 31.61003
Assuming 31.61003 is correct, there are no values in your data exceeding this so no solution is possible.,

[SamT]

Where did


64
9514.62
31.61003


come from?

I got


152
4652.377
30.60774




How about just telling us what a Customer might order and the ideal that you deliver and why that is the ideal?

What has to be within 5 thousands feet? The Total Length or the average length of each piece? I do hope for your sake its each length. 580 feet +/- .06inches is ridiculous.

It might be handy to know how accurately you can cut each piece (of .999 solid Gold 1" bar stock. :jawdown:)

[Paul_Hossler]

I have to literally guess what items we gave them

I don't understand why



, but amazingly with an amazing amount of time I can usually make it work..

Can you explain how you did that in the example to come up with the answer?

[jakesandelin]

Hey guys thanks for the questions!! So honestly that's a snippet of over 159 cells of data. I can't legally give you more than that and even those numbers are fudged a bit. Kind of why this was so hard to explain... also those second sets of numbers are simply what I'm looking for in the range of data. Imagine I'm selling spaghetti noodles... well let's say I have 1000 noodles in like 18 different batches. Sadly because of the way my kitchen is set up I just have the averages of the noddle lengths per batch. Not each specific one. So when I sell 100 of them I find out the exact amount sold and the length of all of them together. I then take the average length per noodle and I try and use that average length per noodle to guess which batch's I sold the customer. It's painfully annoying and is beyond tedious for my already busy work life. But I can't change that process....

so so what I need to find out is what batches together most closely equal that average length. Or what batches can get as CLOSE as possible to the length of all the noodles together (I'm required to have the exact same amount of noodles reported from the batches) I can be within a very small range on the total length. So it is guess work... but I have to make it as close as possible. Did that make more sense? I'm sorry it's so hard to explain :/

[mdmackillop]

Try this. Additional info in attachment

Sub Test()
Cnt = [B2]
[G18] = 50
Range("H1").Resize(20, 2000).ClearContents
DoSort
Application.ScreenUpdating = False
For I = 1 To [B2]
Application.StatusBar = "Iteration: " & I 'debug
Calculate
x = Abs([G1] - [C1])
If x < [G18] Then
If x < 0.005 Then
Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Resize(17).Value = _
Cells(1, 6).Resize(17).Value
End If
Application.ScreenUpdating = True
[G18] = x
[G19] = Cells(1, Columns.Count).End(xlToLeft).Address
Application.ScreenUpdating = False
End If
Next I
Application.ScreenUpdating = True
MsgBox "Best result = " & Format([G18], "0.00000")
Application.StatusBar = ""
End Sub


Sub DoSort()
Dim r As Range
Set r = Range(Cells(3, 1), Cells(3, 1).End(xlDown)).Resize(, 5)
r.Sort key1:=r.Columns(5), order1:=xlAscending, Header:=xlYes
End Sub

[jakesandelin]

Thanks I'm going to take a look at it right now! I'm also going to try and re-describe what the problem is.. I don't think I did the best job of it the first time around.

[jakesandelin]

I looked at it and it doesn't seem to organize it with the 12 closest differences. It instead is just using the 12 lowest averages I believe? I would need the 12 closest averages.

[mdmackillop]

The smallest difference in Averages are the closest Averages.

[snb]

Please post your sample workbook.

[jakesandelin]

Hey guys I am going to try and reexplain my problem! I think I've done a poor job of it.

I have a large amount of data showing me the amount of noodles and their length together. When I sell a customer a bunch of noodles I don't know which batch it came from because of the way my facility it is organized. For recording purposes though I have to do my best to select the best sequence of batch numbers to most closely get the length of noodles I sold the customer. The amount of noodles has to be exact. I've included an excel example I think describes this very well. Remember the amount of batches I have is constantly changing.. so the program can't be defined to just a certain space. I am sorry guys I tried hard to come up with a solution, but this is out of my ability atm.

[jakesandelin]

The smallest difference in Averages are the closest Averages.


I agree! And, your logic made sense and I am trying to see if I can now apply that or utilize that code you gave me. The problem was when i inserted my own data it didn't put the smallest averages closer to the top where it was pulling data. I also wasn't getting lengths close to the final length I need. I think honestly it's entirely my fault though because I gave very bad explanations of my problem. With the new comment and example I gave I believe this will make a lot more sense.

[mdmackillop]

Based on your example; Note "Min" added to Column F formulae

[jakesandelin]

Based on your example; Note "Min" added to Column F formulae

What do you mean? Sorry, I am new to VBA and this site. I looked at the excel doc and it seems the same.

[mdmackillop]

The previous code allowed a greater Quantity if F compared to B. This prevents that issue.

[jakesandelin]

The previous code allowed a greater Quantity if F compared to B. This prevents that issue.

Oh that looks fantastic! I see how that works.. The main concern is sometimes the best solution comes from two items that do not share an avg close to the goal. But, together they perfectly equal the length and the amount required (because they were probably what was actually taken) This method is beautiful but it doesn't actually find the best option it just summarizes the closest results from the items that share the closest averages(NOT AT ALL COMPLAINING!!! BECAUSE I CAN TOTALLY USE THIS TO LEARN) But, sadly I also need to try and keep myself from having to rebundle (where I need to divide my batches up) more than once.. or do the best I can to keep it to only one time. As it takes an amazing amount of paper work each time and also breaks the point of the program a bit.