Create batches based on set quantities and order by earliest date

Hello all,

I have a list of orders for skateboards with the model, date, and quantity for each order. Unfortunately, I can only make 25 skateboards a week. Is there a way I can take the data and create a production schedule with batches of 25 per model and rank them by earliest dates fist?

Thanks!

Ocdawg

Dear OCdawg:

First order first serve or you want to lump together the same model orders in order to optimize production?

Thanks for responding - I'd. Like to rank them by model.

Ocdawg

Dear Ocadawg:

I did this manually just to show if that would be a desirable output. I did realize that it should have started with model 33T because it has the oldest date. It would be taken into account on the real scheme.

Wow, that looks great. Is there a formula to do that?

Ocdawg

I will work on over the weekend...

Below please find the macro that will produce the output in TRIAL
I have included the workbook as well
Have a nice weekend!!

Please Login or Register  to view this content.

Very nice!

You rock!

Ok mark it as solved if you are satisfied. It was a good brain challenge. Any time

Just a warning, it does take care of smaller orders but if you have an order over 80 it needs extra coding... your call

What is the extra coding?

Please Login or Register  to view this content.

With this change the macro can now handle big orders... give it a try..

What do I do after adding the code to a new macro? I hit run, but it only prints "Week 1" on the trial sheet. I'm on Excel 2007.

Thanks,

Ocdawg

I tried this improved macro with a lot size of 100 and it worked. It is writing to the SHEET "TRIAL". I dont see any command that is not compatible with your execl version. Please write if it does not work for you but send me your actual data sheet. It must start in row 3.

Please Login or Register  to view this content.

ot

Hello,

Perhaps I am not executing it properly. I've attached the file I am working with. It only prints "Week 1" on the Trial sheet.

Thanks!

Ocdawg

Try the attached.

Please Login or Register  to view this content.

Hi, I think I found the reason it was not working. Originally I had copied the data into TRIAL manually. Nevertheless, if you want to start in a clean worksheet, TRIAL, I added the necessary code. Data is to be in "SHEET1".

Please Login or Register  to view this content.

You were right, that fixed it! I appreciate your help.

What code would I need to add if I wanted to add columns that list the customer, purchase order and sales order? See attached. Is there a way to have the data output into a vertical display?

Thanks so much.

Ocdawg

I changed the code to fit the 3 new fields.

Could you elaborate on the "vertical display"?
Please include the desired output format (just an small example) so the output can be formatted the way you want...

Check the VERTICAL tab. Is that what you mean by vertical. the ** is when the order is completed.

Yes, that is the vertical type layout. But it doesn't look like it's breaking into groups of 25.

Ocdawg

You are right. It was working but showing the wrong parameter. Take a look at it now...

Very cool!

What code would I add to bring it back to keeping the model groups together for batching?

Ocdawg

The easiest way is to sort the orders by MODEL and then by date so just add the red Highlighted line.

Please Login or Register  to view this content.

Great, thanks. Do you do all this by writing code or recording macros?

How do I mark as solved?

Ocdawg

Originally Posted by Ocdawg

Great, thanks. Do you do all this by writing code or recording macros?

How do I mark as solved?

Ocdawg

I did this by writing code but from time to time I record an action to save time and learn.

Hi rcm,

I see this sorts alphabetically. What would I need to do if I wanted to sort by oldest average date of order?

Ocdawg