Solve : Best way to achieve this??

1.	Solve : Best way to achieve this??
Answer» So every week I have a schedule of employees. Every week I have to manually assign employees to machines and print out a projection of the weeks machine coverage as well as use this as a checklist when manually assigning work to employees on worksheets. This has up until now been a very manual process involving dumb excel spreadsheets requiring redundant data entry and using a web interface to assign and print daily employee worksheets. I took on this job recently as supervisor of maintenance for USPS and want to correct for the inefficiency of the process. One area that will make a big impact is being able to create the employee work schedule and then have that spit out this machine coverage projection which gets populated on Sheet 2 based on information on Sheet 1. Biggest handicap is that this has to be done in Excel since no custom programming can be done and brought in on a thumb drive etc. I can think of so many better ways to achieve this WITHOUT Excel importing a .csv and spitting it out etc. I am operating as a 'user' and have to work within the limited permissions. I know that Excel can be a solution to this, but I feel that my approach to this might make for an unnecessarily large list of logic rules of for example creating a bunch of IF statements that test to see who is working and what hours and then populate Sheet 2 with the best predetermined match up. To do this I have to account for EVERY Combination of employee coverage of 16 employees on 3 shifts. In something other than Excel I would assign weights to the list of qualifications to have a program place the best suited employee as a match up per equipment that needs servicing, and process through in an order that loops through PLACEMENT until all machines and employees are accounted for placement of, but not sure if that can be done in Excel or if I have to account for All Combinations to make the 'IF' logic work? Is there a way to avoid having to create a list of every combination in Excel and achieve this? I'd hate to create every combination of staffing with preselected staffing to machinery if there is an easier way such as using a weights like feature or something else. Open to all suggestions. Excel has VBA macros. Can you use those?Hmm ... NEVER played with VBA macros before but if thats the best direction to go I should dive into it if its going to make this far easier than creating every combination of scheduling to create every output desired. Is there a simple way to test if VBA macros would work? Such as a "Hello World" If so I'll give it a try.Quote from: DaveLembke on September 06, 2012, 12:50:44 PM Hmm ... Never played with VBA macros before but if thats the best direction to go I should dive into it if its going to make this far easier than creating every combination of scheduling to create every output desired. Is there a simple way to test if VBA macros would work? Such as a "Hello World" If so I'll give it a try. I'm not 100% sure how you'd go about it, but it should be better than the alternative. Alt-F11 opens it. You can add a new module and create a Public Sub (eg. "Public Sub AssignWorkers()") then use VBA code to work with the ActiveSheet, add items to cells, etc. Then you would run the macro by that name in the Macros dialog. When I first started reading your post it sounded like a program I wrote for a freelance client a few months ago was exactly what you needed, but when I read the details it doesn't seem like it. I thought I'd mention it here anyway; It's designed to manage the time spent by employees on "Orders" (in my client's case, this was automotive repair, but it would work just as well for PC repair and stuff too); Employees clock into orders (repair orders, that is), do the work, and then clock out. This is useful for both payroll as well as for- (where applicable) charging for the labour cost of the repair. Not relevant to this particular problem but seems tangentially related and I've not done anything with the program in ages so thought I'd throw that out there too.Yah the cooperative food store chain that I use to be head of IT for had a service center and I supported their NapaTracks and Mitchel Ondemand ( web based subscription service for technical INFO ). NapaTracks allowed for customer database with tracking tech times etc, and inventory control. I also had to support their NHOST http://www.nhinspect.com/whatToExpect.html although the state contractor who had 1 to 2 day ETA on repair of this system didnt like when I got involved and fixed the problems on a kiosked OS which i hacked once to add printer drivers for a HP printer vs the Canon that failed. When you have a list of cars needing inspection and a printer fails and inspection could not be performed without printing documentation if your a crafty IT individual like myself you come up with a quick solution to make something work. I was able to hack in with a custom AUTORUN CD that triggered control.exe to bring up control panel which gave me access to installing the printer driver. The kiosk was made with stripped down interface profile, but fortunately it appeared that they left it as admin privileges vs user so I was able to install the drivers with no problems Will give VBA macro a try tomorrow when i am getting paid for my time working on this As for now time to relax and game

Answer»

So every week I have a schedule of employees. Every week I have to manually assign employees to machines and print out a projection of the weeks machine coverage as well as use this as a checklist when manually assigning work to employees on worksheets. This has up until now been a very manual process involving dumb excel spreadsheets requiring redundant data entry and using a web interface to assign and print daily employee worksheets.

I took on this job recently as supervisor of maintenance for USPS and want to correct for the inefficiency of the process. One area that will make a big impact is being able to create the employee work schedule and then have that spit out this machine coverage projection which gets populated on Sheet 2 based on information on Sheet 1.

Biggest handicap is that this has to be done in Excel since no custom programming can be done and brought in on a thumb drive etc. I can think of so many better ways to achieve this WITHOUT Excel importing a .csv and spitting it out etc.

I am operating as a 'user' and have to work within the limited permissions. I know that Excel can be a solution to this, but I feel that my approach to this might make for an unnecessarily large list of logic rules of for example creating a bunch of IF statements that test to see who is working and what hours and then populate Sheet 2 with the best predetermined match up. To do this I have to account for EVERY Combination of employee coverage of 16 employees on 3 shifts.

In something other than Excel I would assign weights to the list of qualifications to have a program place the best suited employee as a match up per equipment that needs servicing, and process through in an order that loops through PLACEMENT until all machines and employees are accounted for placement of, but not sure if that can be done in Excel or if I have to account for All Combinations to make the 'IF' logic work?

Is there a way to avoid having to create a list of every combination in Excel and achieve this? I'd hate to create every combination of staffing with preselected staffing to machinery if there is an easier way such as using a weights like feature or something else.

Open to all suggestions.
Excel has VBA macros. Can you use those?Hmm ... NEVER played with VBA macros before but if thats the best direction to go I should dive into it if its going to make this far easier than creating every combination of scheduling to create every output desired.

Is there a simple way to test if VBA macros would work? Such as a "Hello World"

If so I'll give it a try.Quote from: DaveLembke on September 06, 2012, 12:50:44 PM

Hmm ... Never played with VBA macros before but if thats the best direction to go I should dive into it if its going to make this far easier than creating every combination of scheduling to create every output desired.

Is there a simple way to test if VBA macros would work? Such as a "Hello World"

If so I'll give it a try.

I'm not 100% sure how you'd go about it, but it should be better than the alternative.

Alt-F11 opens it. You can add a new module and create a Public Sub (eg. "Public Sub AssignWorkers()") then use VBA code to work with the ActiveSheet, add items to cells, etc. Then you would run the macro by that name in the Macros dialog.

When I first started reading your post it sounded like a program I wrote for a freelance client a few months ago was exactly what you needed, but when I read the details it doesn't seem like it. I thought I'd mention it here anyway; It's designed to manage the time spent by employees on "Orders" (in my client's case, this was automotive repair, but it would work just as well for PC repair and stuff too); Employees clock into orders (repair orders, that is), do the work, and then clock out. This is useful for both payroll as well as for- (where applicable) charging for the labour cost of the repair. Not relevant to this particular problem but seems tangentially related and I've not done anything with the program in ages so thought I'd throw that out there too.Yah the cooperative food store chain that I use to be head of IT for had a service center and I supported their NapaTracks and Mitchel Ondemand ( web based subscription service for technical INFO ). NapaTracks allowed for customer database with tracking tech times etc, and inventory control. I also had to support their NHOST http://www.nhinspect.com/whatToExpect.html although the state contractor who had 1 to 2 day ETA on repair of this system didnt like when I got involved and fixed the problems on a kiosked OS which i hacked once to add printer drivers for a HP printer vs the Canon that failed. When you have a list of cars needing inspection and a printer fails and inspection could not be performed without printing documentation if your a crafty IT individual like myself you come up with a quick solution to make something work. I was able to hack in with a custom AUTORUN CD that triggered control.exe to bring up control panel which gave me access to installing the printer driver. The kiosk was made with stripped down interface profile, but fortunately it appeared that they left it as admin privileges vs user so I was able to install the drivers with no problems

Will give VBA macro a try tomorrow when i am getting paid for my time working on this As for now time to relax and game

Solve : Best way to achieve this??

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment