Random excel function with no duplicates and unfixed data

Question

I need to translate an old program working on AS/400 that was picking random students to work for my city. I can use any program, as long as it works. To make it simple and fast, i chose excel.

However, i come over a small problem. I need to have no duplicates, because the same student can't do 2 jobs over one summer. Also, i need this to be flexible, since every year, new students will be added and some will be deleted.

This function works almost as much as i would want it: =INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)

The index $A:$A gets all the lines in the column A. So even if i add 20 names, it will take them into consideration. Then it choose randomly a value (the name) between the line 1 and the number of total lines (COUNTA) in the column $A. The problem with this method is that it allows duplicates.

Another function i found was to create a colum full of =ALEA() and then rank these by the numbers. This is not very pretty, but at least, there is no duplicates. The problem comes from my formula, that is static, and that i can't make flexible:

=INDEX($A$2:$A$74,RANK(B2,$B$2:$B$74))

My names are in the colum $A and my random values in colum $B. What i say is, rank the value in B2 (then B3, then B4, etc.) that is found in the column B.

What i would like is to integrate the COUNTA into the second function and (IF POSSIBLE) take the RANDBETWEEN instead of the rank function so that i don't have ugly numbers.

I am opened to use the first function with some kind of duplicate check. As long as the secretary doesn't have to do a lot of manipulation, it should be fine.

Thanks a lot for your help xox

Solution

I've created something in VBA that does what I think you want. Now keep in mind I'm very new with VBA, so it probably isn't the prettiest thing. To be clear, I had 10 names in column A from rows 1 to 10 and then simply ran this subroutine and it generated a list of unique names in column F. Here's my code:

Sub getRandom()
    Do While Application.WorksheetFunction.CountA(Range("A:A")) > 0
        Dim count As Integer
        count = Application.WorksheetFunction.CountA(Range("A:A"))
        Dim name As String
        name = Application.WorksheetFunction.Index(Range("A:A"), Application.WorksheetFunction.RandBetween(1, count))
        Dim row As Integer
        row = Application.WorksheetFunction.Match(name, Range("A:A"), 0)
        Range("F11").Select
        Selection = name
        Rows(row).EntireRow.Delete
    Loop
End Sub

If you wanted to get the names one at a time, just remove the loop. How it works is it does exactly what you did with the INDEX and RANDBETWEEN functions, grabs the name in column A with that generated number, then deletes that row entirely, and thus no unique name is generated.

I chose column F arbitrarily and cell F11 specifically since that cell will not be affected when the rows are deleted.

I hope this helps, and if it's not what you were looking for I'll see if I can enhance it a bit.

Comments

[Maude]

The problem i have with the website you provided is that it is static. Since the formula is :=RandsFromRange(A1:A10,5) , everytime that i add a name, i will have to manually go change the range. Your second answer seems to be the way to go, although it would require the secretary to copy the =rand() for the new lines that she might add.

[Maude]

Thanks a lot for your help Jack, this works like a charm. Just a little question: count= the number of names in column A. name= A random name chosen in the column A. row=what is this for? Range("F11") is to specify you want 10 names Row Delete is to delete the row for no duplicates Now, i do understand that 2 random index might come to the same name but what happens if this name is deleted? Does it just exit the loop ? Also, do you have an idea of a way to only delete those that are actually picked? As if i want to know who has not been picked, i can do it without doing it manually?

[Maude]

I reread your solution and another problem i see is that if,for example, i want 20 students names, and then somebody tells me that i can add 10 more, i might find the same students in the second draw than in the first one. Now this isn't a problem for 20, but when there is over 100 students, it is hard to know if it was picked or not.

[jrad]

Well how it works is it deletes the one who is picked at every iteration, and so whoever is still in the list has yet to be picked. As I said in my post, if you want to get the names one at a time, just remove the loop. As for the row variable, that is for identifying the row number that is to be deleted.

[Maude]

The problem is that let's say i have 16 names and i chose F14, it deletes the whole column A, even if there is names that arent in my F column. To prove this i wrote the name Maude1 to Maude20 in the column A. And then i ran your function. As a result, i get 14 names in random order, but the whole column A is gone, including the one that weren't chosen

[jrad]

Yes, that makes sense. The problem is that since you chose F14, some of the names are being erased in column F after they've been chosen. You need to choose a cell, in this case something below F20, that will not be affected (deleted) when the rows are deleted. That's why I chose F11 in my example; row 11 is never deleted since only rows 1-10 are deleted. Make sense?

[Maude]

I believe i now understand what you tried to do. I was not getting the loop much but basicly you loop into the whole column A. Let's say i want to pick 15 students, This seems to work: Sub getRandom() Dim k As Integer k = 0 Do While k < 15 //Do your stuff here Loop The thing i don't understand is the :Range("F14").Select

[jrad]

The Range("F14").Select denotes where the chosen names are to be placed. Let me take you through a small example: You have these names in column A: Jack, Alex, Jeffrey. In this case, you would do Range("F4").Select since we have three names in total (and 3 + 1 = 4). Say the random choose selects Alex for the first name. Alex is now in cell F4. Then, since Alex was in cell A2, row 2 is deleted, and since it was the ENTIRE row that's deleted, what was in cell F4 (Alex) is now in cell F3, and Jeffrey has moved into cell A2 with Jack staying in cell A1.

[Maude]

I GOT IT! i don't know why, it was so obvious. You were right. By chosing the right range select and by specifying the number of times i want this function to loop, it doesn't delete the unchosen one

[jrad]

Haha okay, glad I could help!