Delivery schedules - creating all possibilities

[andyfresh]

Hi all,

Im currently creating a delivery schedule in excel where by in row 1 between cell a1 and G1 states the day then underneath I would like them to place a 'y' underneath the day which they would like the delivery to be made. They can have between 1 and 7 deliveries a week. So based on this there is a lot different delivery schedules possible. ie monday and wenesday or tuesday and thursday.

I need to create a sheet with all the different variances of schedules to use later on in the system and so I was hoping that someone would know a way of populating this list without typing them all in manually.

Andy

 

[SkipVought]

Hi,

Well first of all you have to have a clear statement of the logic that you will apply in determining the various cases, rather than the approch that I have seen on occasion...

"You, start programming, while we figure out what the requirements should be!"


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[Gavona]

I fully agree with Skip. Do you
really need a list of all of these or just all those selected?
However the following, without code is not too onerous:
There are 128 combinations ( 2 to the power of 7 ).
You have column headings for Mon, Tues......Sat, Sun
0 means no delivery, 1 means delivery.

Under Monday you need 64 1s follwed by 64 0s
Under Tuesday you need 32 1s followed by 32 0s followed by 32 1s follwed by 32 0s
under Wednesday you need blocks of 16 alternate 0s and 1s
Under Thursday blocks of 8
Fri blocks of 4
Sat Blocks of 2
Sunday alternate 0s and 1s

That will give you a table with all possible options. Whether it is what you need to achieve your task, i couldn't say. It would take me longer to figure out the VBA than to just do that the above. It doesn't sound like a repeating task so why bother with code (mind you the above approach has clear structure so should be easily encodeable):
For i = 0 to 6
block size is 2 to power of i
number of blocks is 2 to power 7 divided by block size


Gavin