Need help with Arrays in Excel 1

[Koisti]

I have an array called NEWLIST() which can vary in size depending on how many names are in the list. I would like to pick each name only once in a random order untill all of the names have been picked. Can any one help me with this?

 

[TimMyth]

The easiest way would be to create a second array and place the elements from NewList into the second array in a random order. If the first array has duplicate names, then sort NewList first and do a check on each element to see if it matches the previous element before you randomly place it in your second array. If the list contains duplicate names and can't be sorted, then you'll want to use three arrays. The first is your NewList array, the second is a copy of NewList that can be sorted, and the third is your result array.

 

[Koisti]

Ok, so how would I put them into the 2nd array in random order?

 

[combo]

Assuming thet NEWLIST() starts from 1, output to SORTLIST():

[tt]listsize=Ubound(NEWLIST)
Redim SORTLIST(listsize)
For i=1 To listsize
SORTLIST(i)=NEWLIST(i)
Next i
Randomize
For i=listsize To 2 Step -1
k = Int((i+1)*Rnd)
temp=SORTLIST(i)
SORTLIST(i)=SORTLIST(k)
SORTLIST(k)=temp
Next i[/tt]

 

[TimMyth]

While x <= UBound(NewList) ' While we still have elements in NewList
Randomize ' Initializes Rnd so we don't get the same number
Position = Int(UBound(NewList) * Rnd) ' Get a random number
If NextNewList(Position) <> &quot;&quot; Then GoTo GetNewNumber: ' Make sure we're not using an occupied element!
NextNewList(Position) = NewList(x) ' Put the old element in the new array
x = x + 1 ' Increment our counter
GetNewNumber: ' Skips to here if the element had data
Wend

Of course, I just realized that this may take some time to run. Another possible method would be to instead swap things around inside NewList several times, much like shuffling a deck of cards. This would be accomplished by the following.

Dim Temp As Variant
For x = 0 to UBound(NewList)
Randomize
Position = Int(UBound(NewList) * Rnd)
Temp = NewList(x)
NewList(x) = NewList(Position)
NewList(Position) = Temp
Next x

This is not as random as the first solution, but is MUCH quicker. You could repeat this whole shuffling process a couple of times so that NewList becomes more random. Either one should produce enough of a random order for your needs though.