Formula To Randomise Names

[Dazzawm]

I have a list of names layed out like below. I need a formula please to randomise them so no name is next to each other in the list. Thanks.

Louis Pearce

Louis Pearce

Louis Pearce

Louis Pearce

Anthony Ray

Clive Kelly

Clive Kelly

Clive Kelly

Josh Anderson

Josh Anderson

Josh Anderson

<colgroup><col></colgroup><tbody>
</tbody>

 

[Dazzawm]

I did try the Rand function and sorted by that column but I still had 3 names in successive rows etc..

 

[Phuoc]

Try this:

=RAND()+COUNTIF($A$2:A2,A2)*10000

 

[Dazzawm]

That works well thanks. Would you mind explaining how that formula works please?