Create a master production schedule for a shop & extract specific schedules from it.

[CaboRobbo]

Hi,
My company uses Excel 2007 to create a “master schedule” for our production department, which lists all of our jobs, with their respective operations, on given days. Our real master schedule is hundreds of rows (jobs) by hundreds of columns (a column per day of the week for a rolling 1-2 years at a time.) I need to somehow provide a schedule that is specific to a single operation/machine (only “saw” for example), then another schedule specific to “sanding”, etc., so I can post them at their respective work stations. I am including a short/basic sample of the master schedule, and two desired outputs (sawing & sanding) from the master schedule.

[TABLE="width: 1088"]
<colgroup><col><col span="11"></colgroup><tbody>[TR]
[TD]Job / Routing[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]Job 0001[/TD]
[TD]Load[/TD]
[TD]Measure[/TD]
[TD]Sawing[/TD]
[TD]Sanding[/TD]
[TD]Gluing[/TD]
[TD]Drying[/TD]
[TD]Router[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0002[/TD]
[TD][/TD]
[TD]Load[/TD]
[TD]Measure[/TD]
[TD]Sawing[/TD]
[TD]Sanding[/TD]
[TD]Gluing[/TD]
[TD]Drying[/TD]
[TD]Router[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0003[/TD]
[TD][/TD]
[TD][/TD]
[TD]Load[/TD]
[TD]Measure[/TD]
[TD]Sawing[/TD]
[TD]Sanding[/TD]
[TD]Gluing[/TD]
[TD]Drying[/TD]
[TD]Router[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Load[/TD]
[TD]Measure[/TD]
[TD]Sawing[/TD]
[TD]Sanding[/TD]
[TD]Gluing[/TD]
[TD]Drying[/TD]
[TD]Router[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Load[/TD]
[TD]Measure[/TD]
[TD]Sawing[/TD]
[TD]Sanding[/TD]
[TD]Gluing[/TD]
[TD]Drying[/TD]
[TD]Router[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 732"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]Sawing Op[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]Job 0001[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sawing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sawing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sawing[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sawing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sawing[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 821"]
<colgroup><col><col span="8"></colgroup><tbody>[TR]
[TD]Sanding Op[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[/TR]
[TR]
[TD]Job 0001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sanding[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sanding[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sanding[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sanding[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 0005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sanding[/TD]
[/TR]
</tbody>[/TABLE]

I am familiar with CAD programs (ex. AutoCad) that utilize layers to help manage different necessities. In architecture for example, a house’s foundation would be drawn on one layer, the structure drawn on another layer, and the electrical schematic drawn on yet another. In the CAD program, one can look at a single layer, all layers at once (all in scale atop each other), or any combination between.

I am wondering if there is something similar in Excel. Maybe my saw operation on one layer, sanding operation on another, etc., all on the master schedule, with the ability to view/print one or multiple layers. I’m dreaming here…..

Does anyone know of a way to somehow accomplish what I need to get to? Maybe a worksheet per work operation? I’m open to any and all ideas. Any suggestions/help would be greatly appreciated! Right now, I just do all of it manually by copying to worksheets and deleting the undesired info. Very time consuming! I would rather just manage the single worksheet containing the master schedule.

Thanks for any help you can provide.
Rob

 

[SourabhGupta]

Hello,
I have applied the formulas....Here's the download link--

http://we.tl/xHPUwGmCY1

You just need to enter the name of the activity in cell A1 of Result Sheet....like sanding, sowing, etc
Hope it helps...
Regards
Sourabh Gupta

 

[Qing Liu]

Here is a quick and dirt solution demonstration:

1) Assume the range of A1:L6 hosts your sample master schedule data.

2) Create a list of operation names in the range of B8:B14, such as Load, Measure, ..., Router

3) The range of C8:C14 will be used to set the operation(s) to be presented in your extract schedule (by inputting the text of "SHOW"). Multiple operations are allowed to extract.

4) Copy the range of A2:A6 to the range of A16:A20

5) In the cell B16, type the following formula:
=REPT(B2,SUM(($B$8:$B$14=B2)*($C$8:$C$14="SHOW")))

This formula must be confirmed by Ctrl+Shift+Enter, instead of Enter only

6) Copy the formula through to the whole range of B16:L20

Hope that this helps.

 

[CaboRobbo]

Thank you! I will download and try it when I get home tonight.

 

[CaboRobbo]

I'll try this one too once I get get home tonight. I appreciate your help. Will advise....

Regards,

 

[CaboRobbo]

Thanks to you both for your help. I like both options. Now that I have fiddled with them at home, I will take them to work tomorrow and begin implementing each method to see how it goes.
I really appreciate the help from each of you.
Best regards!
Rob