Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making random and unique selections from a list
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making random and unique selections from a list
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. "Babymech" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making random and unique selections from a list
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 he http://www.sulprobil.com/html/uniqrandint.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Random List | Excel Worksheet Functions | |||
Making list of unique items from two different columns | Excel Worksheet Functions | |||
Unique random numbers from list | Excel Discussion (Misc queries) | |||
how to identify unique list of 200 random entries from a list of 3 | Excel Worksheet Functions | |||
Making list with unique columns | Excel Worksheet Functions |