title: Random excel function with no duplicates and unfixed data
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>This function works almost as much as i would want it:
  <strong>=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)</strong></p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <p><strong>=INDEX($A$2:$A$74,RANK(B2,$B$2:$B$74))</strong></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Thanks a lot for your help xox</p>