Data Cross Checking from imported online poll

Hi,

I'm trying to create a schedule in Excel based on the team's availability imported from Doodle.

I have attached the documentDoodle 16.xls 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!!!

Sure, I thought it was visible, the link might be in the first paragraph, sorry.


Doodle 16.xls

Oh I'm sorry. It was in the first paragraph. My bad vision glossed over it. My apologies!

---

If you found this post helpful, please say thank you by clicking the Add Reputation button. Thank you

Well...this is a tough one because what you're really doing is a puzzle. Are you currently visually checking the best pairings? So here's one quick thing I came up with that would still require leg work but may eliminate at least a little bit of wasted time:
1) convert "ok" to "1" using replace all feature
2) do a subtotal at the bottom
3) apply a filter
4) in cell B2 or somewhere near the top/left, put this formula: SUMIF(B22:AE22,"1",B22:AE22)+SUMIF(B22:AE22,"0",B22:AE22)
5) filter pairs

Here is what I did: PairingsExample.xls

You'll still have to click through each pairing....but you won't have to scroll left/right. For example, it took me about a minute to find Edith Sipos as the best pairing with Adela Muresan (only 6 time slots that they aren't paired together).

However, that doesn't mean there isn't a better pairing for Edith.

It's quite a big puzzle you have. I will continue to think about it. I hope the name list isn't too much longer than this or my possible time saver won't help much. Good luck

---

If this post has been helpful, or has at least brought a smile to your face, please say "Thank You" by clicking Add Reputation. Thank you!

Thanks so much, I'm going to try these options now, see what fits best. I'll keep you posted!