Sort Based on Criteria

Hi all

Some help on this one would be really appreciated. I've attached a sample spreadsheet. I was wondering if there was a possible VBA or Macro code, which code help me. I thought about using a normal formula but couldn't get it to work.

I basically want to randomly sort the names and assign different days to them. You'll see on the attachment, I have a list of names against days and want to assign days to different names. However, I want to be re-sort column B (tried RAND but it wasn't the perfect solution) against days. However, I don't want any names to appear more than twice in a row and I want the ability to stop certain names landing on certain days. So for example, I would like 'Kate Winslet' not to appear on Mondays or Thursdays.

I hope this makes sense.

Thanks in advance, for the help.

Thanks for your help with this. I've managed to find a work around, but I might use your code, as it may make things easier.