Making random and unique selections from a list

[Babymech]

I posted a question similar to this before, but I probably described my
situation very poorly, which is why I haven't gotten any answers - I've
worked on it myself now and haven't solved it, but I think I might be able to
describe the problem better now.

I have a list of 20 non-repeating strings in Column A. I have a list of 20
integers from 1 to 10 in Column B, some of which repeat. I have another list
of 20 integers from 1 to 10 in column C, which also repeat sometimes.

I want to design a function that:

a) randomly selects a string from the list in column A. Right now I've done
this in the following way, and it seems to be working:

RandomNumber = Int((20 - 1 + 1) * Rnd + 1)
RandomListMember = Cells(RandomNumber, 1)

Are there better ways of doing this?

b) Then I want the program to write this randomly selected string to a cell,
and then select another string randomly - but it can't be a string that's
been previously selected. This one I've had more problems with and would like
help on. What I've thought so far is that my function could do the following:

1) Print the first selected string to cell 1 in range "E1:G10"

2) Select a new random string from column A

3) Scan "E1:G10"; if the new random string already exists in that range,
then goto 2.

4) Print the randomly selected string 2.

And then go through that 20 times. One of the problems with that method is
that it can repeat step 3 for a very long time when setting the final cell,
since it only has a 1/20 chance of getting to step 4. What I need here is a
better method, and code to implement it.

c) Finally, and this adds a level of complexity that might mean I have to
change the first two steps, I want to be able to check the randomly selected
strings and see which integers exist on the same row as that string in
Columns B and C. I want my function to tell me how many of the strings I've
selected have the same values in either column B or column C; if, for
example, I select StringA from "A5" and StringB from "A10," I want my
function to compare the value in "B5" with the value in "B10" and the value
in "C5" with the value in "C10," and tell me how many matches there are (0-4
possible matches). Ideally this could also be a criterion to feed into the
random selection - if I'm only making a selection of 5 strings from Column A,
I only want to accept a selection where the total number of matches in the
end is 5 or higher.

I know this isn't a well delimited question, but that's because I believe
that the solution for the whole problem is in the planning, not just the
coding of any individual part (though I don't know the individual coding
either). Hopefully you can help with both coding and planning.

 

[Joel]

See Answers below

a) randomly selects a string from the list in column A. Right now I've done
this in the following way, and it seems to be working:

RandomNumber = Int((20 - 1 + 1) * Rnd + 1)
RandomListMember = Cells(RandomNumber, 1)

Are there better ways of doing this?

Answer : this is a little simplier

RandomNumber = Int(20 * Rnd) + 1
RandomListMember = Cells(RandomNumber, 1)

b) Then I want the program to write this randomly selected string to a cell,
and then select another string randomly - but it can't be a string that's
been previously selected. This one I've had more problems with and would like
help on. What I've thought so far is that my function could do the following:

Answer: The best way of getting non repeated random items is to use a new
column in your worksheet and place the random function in that column so you
will have 20 different number in the new column. then sort by the random
number. Yoi 1st randomm string will be in the 1st row, the 2nd in the 2nd
row and so on.

1) Print the first selected string to cell 1 in range "E1:G10"

2) Select a new random string from column A

3) Scan "E1:G10"; if the new random string already exists in that range,
then goto 2.

4) Print the randomly selected string 2.

And then go through that 20 times. One of the problems with that method is
that it can repeat step 3 for a very long time when setting the final cell,
since it only has a 1/20 chance of getting to step 4. What I need here is a
better method, and code to implement it.

c) Finally, and this adds a level of complexity that might mean I have to
change the first two steps, I want to be able to check the randomly selected
strings and see which integers exist on the same row as that string in
Columns B and C. I want my function to tell me how many of the strings I've
selected have the same values in either column B or column C; if, for
example, I select StringA from "A5" and StringB from "A10," I want my
function to compare the value in "B5" with the value in "B10" and the value
in "C5" with the value in "C10," and tell me how many matches there are (0-4
possible matches). Ideally this could also be a criterion to feed into the
random selection - if I'm only making a selection of 5 strings from Column A,
I only want to accept a selection where the total number of matches in the
end is 5 or higher.



I know this isn't a well delimited question, but that's because I believe
that the solution for the whole problem is in the planning, not just the
coding of any individual part (though I don't know the individual coding
either). Hopefully you can help with both coding and planning.

 

[Bernd P]

Hello,

You are right. The naive trial and repeat approach could cause very
long runtimes if you want to get all numbers of a long list. But there
are some solutions for this. One possibility:

An example:
Sub test()
Dim v
Dim i As Long

v = VBUniqRandInt(20, 20)

For i = 1 To 20
Debug.Print Cells(v(i), 1)
Next i

End Sub

My UDF VBUniqRandInt you can find here:
http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd