Macro to Fill Cells With Random Values From A List

I have a list of values on 1 sheet A2:A14 that I would like to be able to use to generate a new list in a random order in another column. I have played with the Rand function but still haven't figured out a way to do this. Is this possible to do or just wishful thinking? If it is possible I would also like to make sure that no 2 adjacent cells have the same random value. I was hoping to add this macro to a button to generate a new random list whenever it is clicked.

Thanks for any help.

Thanks, this is what I was thinking of but as you mentioned there is the chance of multiples. Is there any way to randomly select a cell and then have that cell never come up again? For example if the rand() returns a3 then remove a3 from the range to be selected for the next rand() call. I know it's a big ask and was thinking that it would probably involve a huge nested if statement.

Thanks for getting me started in the right direction though.

Rick, it was also about:

Originally Posted by brent_milne

If it is possible I would also like to make sure that no 2 adjacent cells have the same random value.

Artik

Originally Posted by Artik

Rick, it was also about:

The code I posted will never have duplicate values anywhere in the outputted list.

I understood the task in such a way that the same values cannot occur next to each other ( like in the picture).

Example.png

Artik

Originally Posted by Artik

I understood the task in such a way that the same values cannot occur next to each other ( like in the picture).

You are right, my code is producing duplicates. The reason? I did not look at the OP's existing data carefully enough to see that there are duplicate values in his original data... I had assumed the existing data was unique. Given that the original data is not unique, my posted code does not and cannot work given that all it does is shuffle the existing data randomly. Thanks for persisting so that I eventually looked back to see my erroneous assumption.

🥂🥂

Artik

Thanks for your replies, I took a little bit from both of them and somehow managed to add other pieces to make it work. I did change my original data but thanks again for pointing me in the right direction.

Not sure if this gets at your question, but the following functions can be used to either select data, without replacement, from a column of cells (a row if you use TRANSPOSE) or to generate a set of indices:
Option Explicit

'-------------------------------
Function SampleCellsNoReplace(data As Range, ByVal SampleSize As Long) As Variant
'Returns a sample, without replacement, of Samplesize from a column of cells
'PLEASE retain all comments:
'Written and posted 1999/4/1 to microsoft.public.excel.misc by David J Braden
'Originally posted as "HGSample"

Dim hiP1 As Long, i As Long, j As Long
Dim ret() As Variant, temp As Variant

Application.Volatile 'comment out this line for single shot

temp = data
hiP1 = data.Rows.Count + 1
'If SampleSize > UBound(temp) Then SampleSize = UBound(temp)
ReDim ret(1 To SampleSize, 1 To 1)
For i = 1 To SampleSize
j = i + Int(Rnd * (hiP1 - i))
ret(i, 1) = temp(j, 1): temp(j, 1) = temp(i, 1)
Next i
SampleCellsNoReplace = ret
End Function


Function SampleNoReplace(ByVal lo As Long, ByVal hi As Long, ByVal SampleSize As Long) As Variant
'Returns a sample, without replacement, of SampleSize from the range lo to hi
'Written 1999/7/7 David J. Braden
'PLEASE retain all comments: Originally posted to microsoft.public.excel as HGSample2a

Dim hiP1 As Long, i As Long, j As Long
Dim ret() As Variant, temp As Variant

Application.Volatile 'comment out this line for single shot

'The following allows more flexible specification of the support.
If lo > hi Then temp = lo: lo = hi - 1: hi = temp Else lo = lo - 1

ReDim temp(1 To hi - lo)
For i = hi - lo To 1 Step -1
temp(i) = i
Next
hiP1 = UBound(temp) + 1
'If SampleSize > UBound(temp) Then SampleSize = UBound(temp)
ReDim ret(1 To SampleSize, 1 To 1)
For i = 1 To SampleSize
j = i + Int(Rnd * (hiP1 - i))
ret(i, 1) = temp(j) + lo: temp(j) = temp(i)
Next i
SampleNoReplace = ret
End Function

Hope this helps