Options to a pivot table from imported data

[carmenpt]

Hi all,

I'm trying to create a schedule in Excel based on the team's availability imported from Doodle. Ideally, after deciding on the pairs that would work opposite shifts, I would assign them to a location.

Then, using a pivot table, I am trying to determine whether these are the best pairs, but I cannot find a formula or sorting option to show this compatibility.
For example, Employee 1 and Employee 6 should always work opposite shifts on the same location. Screenshot 2015-05-20 16.06.32.png

The second part is, once I am certain of the availability, to import the time periods into the final schedule, but I don't know how to do this.
For example, upon deciding that Employee 1 does an AM shift on Day1, Employee 6 does a PM on Day 1, I leave the OK in the corresponding boxes. But how do I then use the OK as a 'tick' for the time period and import the name of the employee in the time period they are assigned to instead of the OK?

This is how the final schedule should look like..
Screenshot 2015-05-20 16.32.20.png

Many many thanks in advance, and I hope this makes some sense to someone.

[newdoverman]

Pictures are not very helpful. Some of us cannot even see them.

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[carmenpt]

Hi,

After reading this again, I can see how unclear it is, apologies!!

I have attached the document and I will try to explain my thought process in steps. Hopefully you'd be able to help me translate this into a coherent spreadsheet.

Step 1 - Availability - poll sheet

I have imported a spreadsheet from Doodle, containing the team's availability.

Step 2 - Cross Check sheet

I would manually cross reference pairs of two based on their availability. For example, the first two on the list are unavailable on the same shift three times.
Then I'd fill in another table with the results of this cross reference, manually, trying to find the best matches, the pairs of two team members that have the least overlaps in unavailability.
This is the step that I need most help with, as I am sure there is a faster way to do it..

Step 3 - Locations + Schedule sheet

After finding out which team members are most compatible, I'd pair them and assign them to a location. Further, I would sort them based on Attachment 396210location, so that the pairs of two are next to one another.

Then, the pairs should, ideally, have no overlap in unavailability, thus making the shifts assignation very easy. I will show this with rows 17 and 18. I have marked in red the shifts that are not covered, as the cross check was at random and they would definitely be a bad match.
But it's a good example, as it illustrates exactly what I am trying to avoid here.


Any chance this can be done easier, please?
I am to schedule a team of 80 volunteers, and this way is extremely time consuming.

Many many thanks in advance!!!






Doodle 16.xls