Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Mt. Everest of requests
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Mt. Everest of requests
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 On Jun 13, 10:14*am, "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. Mick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Mt. Everest of requests
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Mt. Everest of requests
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Mt. Everest of requests
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 nice requests | Excel Programming | |||
Excel requests the installation disc on startup !! why ? | Excel Discussion (Misc queries) | |||
Exceeding Cell Limit with HTTP requests | Excel Discussion (Misc queries) | |||
Excel suddenly requests the instal disk! | Excel Discussion (Misc queries) | |||
template for check requests | Excel Worksheet Functions |