The Mt. Everest of requests

[Vacuum Sealed]

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.

Mick

 

[Paul Robinson]

Hi
There is a rough first go at putting down a few thoughts about what
you might want to think about there.
Absolutely no chance anyone is going to suggest a solution to your
problem yet. You need to be much more precise and break down your
problem into small pieces when you have that precision.
regards
Paul

 

[Vacuum Sealed]

Hi Paul & Thx for your reply.

Here's my attempt at a slightly more indepth explanation:

Step.1

Individually, Filter/Select all rows from each Zone.

We will use Zone "N"orth for the starting example.

Step.2

Further Filter the selected "N" rows for each Dest(s)

We will use Dest "H" for this stage.

Step.3

Look at all of the rows that match the above filtering and place them into
groups where the Stacks sum to 22 & or where the Weight does not exceed
24.5t....

When a summed combination is found, place a consecutive (Groups number) in
Column "AI" so that those share the all the same Grouping, and then the next
group of 22 would have the next consecutive (Grouping number).

Then repeat for each of the remaining combinations, placing incremental
numbers for the groups to match....

Sorry, this is the best I can break it down, so if no-one can make head or
tail, I will understand and drop the idea...

Cheers
Mick.

 

[Martin Brown]

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

 

[Vacuum Sealed]

Thank you Martin

Dynamic programming solution
Unbounded knapsack problem
If all weights (w1,...,Wn,W) are nonnegative integers, the knapsack problem
can be solved in pseudo-polynomial time using dynamic programming. The
following describes a dynamic programming solution for the unbounded
knapsack problem.

The Knapsack/Rucksack problem is exactly what I am looking for, and I
suppose my next biggest problem is to find an equivelant VB formula to make
it come to life...

Appreciate the pointer

Mick