Formula or Macro help?

I am working on a load plan sheet for new associates in our business. We want to maximize the trucks that are being shipped. We have two different sets:


Each one has a min and max weight, our goal is to have as many close to the max, but also looking at the last one keeping it as high as possible. This means that the other trucks may be closer to the mins then the max's to make the last truck as heavy as possible.

I have attached a sheet with comments and examples of what i am trying to do. I am not sure if its best to do formulas, or a macro to get this done. The min and maxs can change from time to time, and the weight of the load that comes in changes sometimes 100,000lbs sometimes 500,000lbs and it has to be broken up. This sheet is going to be used to help the new associate understand the best way to break it up so we maximize our truck weights and have a higher average.

What we want to make sure of is the last truck which is always smaller, is as big as we can make it, with out taking the other trucks below the mins. This keeps the average higher over a period of time.

If anyone has suggestions please help, I know what i want it to do, just can't figure out how to do make it use the logic.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

In your FCA example you chose 40/30/30/30 but why not 39/31/30/30 or 35/30/30/35? Should the first one always be the (equal) biggest?
dn920Author Commented:
Stephen, I did it just to make it simple for myself, it could be any combo as long as it keeps it above the mins. A lot of times you will see a last truck that can't get to 30k, it will be 14k, but newer associates make the other trucks 45,000, when they could cut off some from these trucks to raise the 14k higher in example.

Basically what ever the lump sum total of the order it needs to break down to keep as many as possible over over the mins, and what ever is left as close to the min as possible with out taking the others under.

Hope this makes sense?
Yes, I think so, but needs a little thought. Others may jump in in the meantime.
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

I just had a look at this again and a couple of problems arose. One, I can't see a difference in your example between FCA and CIF. The target appears to be the same? Two, I'm struggling because of the lack of constraints - there seem ot be an infinite (slight exaggeration maybe) number of ways of solving each problem. May just need more examples, perhaps it will become clearer.
dn920Author Commented:
The only difference between the two are the max and min weights. CIF has a min 45000 and max of 46500, and FCA has a min of 30000 and a max of 45000.

You're having the same problem I am having. The basic concept that I have been messing with is dividing the total weight B2 (what ever the weight may be) then to look at the last truck in this case 4, if this is less than min taking some weight off of the other trucks (some how telling it to not drop those below the min weights) and adding that weight it took from the others to the last truck.

I have seen this done in Excel before, but I can not figure out how they did it. I started to write some VBA Macro code but I couldn't get that to work it would break it down and leave the last truck at what ever was left.

Had a play and came up with something, but not sure if it's right.
Sub x()

Dim nMin As Long, nMax As Long, nDiff As Long, nRow As Long

Sheets("FCA Example").Activate

nMin = WorksheetFunction.RoundUp(Range("B2") / Range("L4"), 0)
nMax = WorksheetFunction.RoundUp(Range("B2") / Range("J4"), 0)

Range("E3").Resize(nMin).Value = Range("J4")
nRow = nMin

Do While nRow <= nMax
    nDiff = Range("B2").Value - Range("G9").Value
    If nDiff > Range("L4") Then
        Range("E3").Offset(nRow) = Range("J4")
        nDiff = nDiff - nMin
        nRow = nRow + 1
        Range("E3").Offset(nRow) = nDiff
        Exit Sub
    End If

End Sub

Open in new window


Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dn920Author Commented:
Stephen, this looks like it works and serves the purpose with out a doubt, I was trying to do this backwards by looking at your coding, it takes out some of the logic but still makes them have to validate the load plan.

So using this, if I want to change it to the CIF min/max I just have to adjust the ranges and it would then look at the CIF max/min correct?

Just want to make sure I am looking at this right. Thank you so much for your help!
For CIF, you would only need to change line 5 to activate the other sheet. Tbh the code could be tidied up, or could be turned into a function so you could pass arguments such as FCA/CIF (even min/max/total).
dn920Author Commented:
Thank you so much, I have been racking my brain on this one for a few weeks trying things. Your help is greatly appreciated and very easy to follow!
Glad it's of use.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.