ernesto123
New Member
- Joined
- Jan 8, 2014
- Messages
- 1
Hello,
I need help...
I have a list of x number of names in a column (at least 50, but the list could contain 300 or more names including empty cells), and I want the names to be "randomly" sorted with one name at a time being selected and output into a separate cell, without repeating, for up to 30 selections. After 30 of the names have been selected, including blank cells in the column, I'd like the formula (or code) to repeat the process of "randomly" selecting any 30 names/cells on the list (including names/cells from the last cycle), but just not repeating any of the cells for that cycle of 30 selections.
Currently, I'm using a couple formulas to randomly select any of the cells in a column including empty cells, but there's too much repetition, even with a list of 100 or more cells. I just want something to break up the possible repetition, but still be able to select those cells after 30 or so selections. Here are the formulas I'm currently using, but both end up giving me too much repetition:
=OFFSET(Sheet1!$A$6,RANDBETWEEN(1,COUNTA(Sheet1!$A:$A)-1),0)
=INDEX($B:$B,RANDBETWEEN(1,COUNTA($B:$B)),1)
I prefer working with formulas, but if code would be the easier fix, then that works too. By the way, I'm not a formula or code guru...
Any help would be much appreciated!
I need help...
I have a list of x number of names in a column (at least 50, but the list could contain 300 or more names including empty cells), and I want the names to be "randomly" sorted with one name at a time being selected and output into a separate cell, without repeating, for up to 30 selections. After 30 of the names have been selected, including blank cells in the column, I'd like the formula (or code) to repeat the process of "randomly" selecting any 30 names/cells on the list (including names/cells from the last cycle), but just not repeating any of the cells for that cycle of 30 selections.
Currently, I'm using a couple formulas to randomly select any of the cells in a column including empty cells, but there's too much repetition, even with a list of 100 or more cells. I just want something to break up the possible repetition, but still be able to select those cells after 30 or so selections. Here are the formulas I'm currently using, but both end up giving me too much repetition:
=OFFSET(Sheet1!$A$6,RANDBETWEEN(1,COUNTA(Sheet1!$A:$A)-1),0)
=INDEX($B:$B,RANDBETWEEN(1,COUNTA($B:$B)),1)
I prefer working with formulas, but if code would be the easier fix, then that works too. By the way, I'm not a formula or code guru...
Any help would be much appreciated!
