On 13/06/2011 10:14, Vacuum Sealed wrote:
Hi All

Was wondering if anyone had any ideas to help consolidate groups of rows so
that they sum to a total given a specific criteria.

The basics...

Zone(s) = ( Column "Y" ) = "N","S","E","W"
Dest(s) = ( Column "K" ) = "H","L","N","R"

Stacks = ( Column "N" ) = 22 (Planned for), but can be as many as 24 Stacks
per load after manual fine tuning.

Weight = ( Column "O" )<24,500

Firstrow = .UsedRange.Cells(6).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row ' Can be
anything upto 350 rows

What I hope to achieve with the above is to generate groups of records
starting from each of the compass points and finishing at each of the
destinations, where the load size equates to 22 stacks and does not exceed
24.5t....

Was thinking that when a group is found (which may contain anything from 1
record to 10 depending on stacks), the code could place a number in Column
"AI", say starting at 1, then incrementing for each ensuing group....

The actual outcome of each of these groups does not have to be 100% accurate
as I would still have to do some fine tuning.

Also, Columns "A","C","F","I:J","L:M","R:V" are all hidden so would have to
be restored to visible state, then hidden again once code has been run.


Mountains of Thanks in Advance.

Spec is a bit flaky but you appear to be asking for a solution of the
Knapsack problem - which is only possible exactly for very modest sized
problems. You will have to live with an approximate solution. eg

http://en.wikipedia.org/wiki/Knapsack_problem

Regards,
Martin Brown