Filling rows that are empty that are not all consecutive in a list with from another list

Any help would be greatly appreciated, I am sure this should be simple but I just can not see how to do it.

My aim it to pull out the text in some selected cells from a column , randomly mix them up and then put them back in a new order sandwiched in between rows that are not changing position, I create a results table to uncomplicated things. The rows that are to be mixed up are in blocks of 8 rows but may well have a set of 8 rows between which will not have been selected to be sorted. The choice of which groups of 8 requiring sorting will change from time to time by a manual user.

So far I have coded it to extract a copy of the contents of the row/cells to be randomly sorted (the number of rows will always be a fixed number but with groups of 8 can change). I then use RAND to apply a randomly generated number and copy and paste the results as a value to fix the new order which can then be used to sort the order of the text using the RAND number

Then I am stuck! I can put the rows into the result list that have not changed order using the indicator (<>"x") I used to extract the rows for sorting. I just can not figure out how to populate the result list with the sorted rows when the result rows are not all consecutive. I have tried using an Array which is fine for the first block of 8 but as soon as the next block of 8 follows rows that have remained unchanged it does not select the next available text row from the sorted list.

I am happy to have a formula or a Macro. I already do the selecting the rows and performing the random sort in a macro.

Problem.JPG

Hi Frieden, welcome to the forum! This macro assumes Exercises are listed in column B, with Sort marked in column C:

Please Login or Register  to view this content.

It almost sounds like the code was interrupted. Were you stepping through at the time?

OK, I'm guessing the "empty" cells in the Sort column aren't actually empty. Do (or did) they have formulas that return empty strings ("")?
Regardless, this would fix that:

Please Login or Register  to view this content.

Sounds like the sorts and the formulas don't play well together. Can you upload the workbook?
(Remove any personal or proprietary information.)

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now click Browse, find your file, then click Upload. Simple!

OK, clearly the sort order in the "X" column is the issue, so I added a test for it in the randomizing formula:
[Less than "X"] gets a 0, [equals "X"] gets a random number (0<n<1), and [greater than "X"] gets a 1
Thus anything NOT EQUAL to "X" retains its position during the randomizing sort.

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

...One last enhancement - In this version, the formatting is restored after sorting:

Please Login or Register  to view this content.

Hope this is helpful, I enjoyed the challenge. Take care - Lee