Data Shuffling (with a Condition) in Scattered Cells (Selection)

Hi all,

Let's say I have a list of non-unique personnel names in A1:A60 (Sheet2) and scattered and selected cells like A2, B3, C1, E5 etc. (Sheet1.)

How do I distribute (shuffle, randomize etc.) these personnel names (sheet2) in selection (sheet1) with this condition:

If the personnel name is non-unique then after shuffling, the number of rows between same personnel names must be 2 or more. For example, after shuffling, A2 and B3 in sheet1 shouldnt contain the same name. A2 and E5 could contain the same name because there are sufficient number of rows between A2 and E5.

How can I do that with VBA and if possible, how can I add additional conditions to shuffling?

Thanks in advance.

Originally Posted by xladept

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.

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

View Pic

Thanks for interest.

Here is a sample workbook to visualize for what i was trying to explain. I've changed some things.

Instead of non-unique names, I've used "Shift Count" to show you how many hours a personnel will work for a day.

If a personnel will work at 00:00 - 02:00 shift then his/her next shift should be 4 hours laters at least (06:00 - 08:00 or later). If personnel count isn't enough, this condition can be 2 hours except between 00:00 - 06:00 (night shift).