Create random list - dublicates are okay but not right after each other

[sawa]

Hi

I want to create a random list based on the numbers 1,2,3,4. The list should contain 100 items, so dublicates are alright - but not right after each other.

Meaning:
1,3,4,2,3...: Is ok.
1,3,3,4,2...: Is not ok. The number 3 occurs twice right after each other.

I have tried to mingle around with a lot of different versions of =randbetween(), but not yet to my satisfaction.

Any tips or good advice are much appreciated.

Thanks

 

[Claus Busch]

Hi,

Am Thu, 11 Sep 2014 11:24:30 -0700 (PDT) schrieb sawa:

Meaning:
1,3,4,2,3...: Is ok.
1,3,3,4,2...: Is not ok. The number 3 occurs twice right after each other.

try:

Sub Test()
Dim i As Long

Cells(1, 1) = Int(Rnd() * 4) + 1
For i = 2 To 100
Do
Cells(i, 1) = Int(Rnd() * 4) + 1
Loop Until Cells(i, 1) <> Cells(i - 1, 1)
Next
End Sub


Regards
Claus B.

 

[sawa]

Thanks a lot. It works just fine.

Initially I was quite keen on making it work by not using VBA, but I guess that is not possible(?).

Thanks again.