|
unique name list
Excel 2003. I have a list of 40 names. I would like to randomly select names
without repeat until all 40 names have been selected. Any ideas please. |
unique name list
Hi,
you can use data Validation function with the formula =COUNTIF(A:A,A1)=1 Regards, Vishu "CJ" wrote: Excel 2003. I have a list of 40 names. I would like to randomly select names without repeat until all 40 names have been selected. Any ideas please. |
unique name list
Maybe this site can help you:
http://www.mcgimpsey.com/excel/randint.html -- Regards! Stefi CJ ezt Ă*rta: Excel 2003. I have a list of 40 names. I would like to randomly select names without repeat until all 40 names have been selected. Any ideas please. |
unique name list
I don't know how that works. If COUNTIF returns the value in cell A1 the
first time then on the second request I need to see the content of cell A2, and on the third request I content of cell A3 etc. When all the cells in the array have been viewed then the array will be shuffled and the displays begin at cell A1 again. At present countif(A:A,A1) = true. "vishu" wrote: Hi, you can use data Validation function with the formula =COUNTIF(A:A,A1)=1 Regards, Vishu "CJ" wrote: Excel 2003. I have a list of 40 names. I would like to randomly select names without repeat until all 40 names have been selected. Any ideas please. |
unique name list
Try this:
http://img3.imageshack.us/img3/6234/nonamek.png Micky "CJ" wrote: Excel 2003. I have a list of 40 names. I would like to randomly select names without repeat until all 40 names have been selected. Any ideas please. |
unique name list
"CJ" wrote:
Excel 2003. I have a list of 40 names. I would like to randomly select names without repeat until all 40 names have been selected. One way.... Suppose the 40 names are in A1:A40. Put =RAND() into B1:B40. Then put the following formula into C1 and copy down through C40: =INDEX($A$1:$A$40,RANK(B1,$B$1:$B$40)) If you do not want B1:B40 changing every time a cell is edited, see my response in your thread "volatile v non-volatile". Alternatively, if you are doing this one time or infrequently, set up B1:B40 as above. If you use the volatile RAND() function, you might want to copy B1:B40 and paste-special-value over B1:B40. Then select A1:B40 and use Data Sort to sort column B. (The paste-special-value is not really necessary. Sort will work just fine. But the volatile RAND() expression will cause B1:B40 to be recalculated when Sort writes back the sorted values. So B1:B40 will no longer reflect the order in A1:A40. No harm done. But it might be mystifying to the unwary user.) |
unique name list
Although the chance of repetition two identical RAND() results is like
winning the Lotto - can one assure it won't happen ? Micky "Joe User" wrote: "CJ" wrote: Excel 2003. I have a list of 40 names. I would like to randomly select names without repeat until all 40 names have been selected. One way.... Suppose the 40 names are in A1:A40. Put =RAND() into B1:B40. Then put the following formula into C1 and copy down through C40: =INDEX($A$1:$A$40,RANK(B1,$B$1:$B$40)) If you do not want B1:B40 changing every time a cell is edited, see my response in your thread "volatile v non-volatile". Alternatively, if you are doing this one time or infrequently, set up B1:B40 as above. If you use the volatile RAND() function, you might want to copy B1:B40 and paste-special-value over B1:B40. Then select A1:B40 and use Data Sort to sort column B. (The paste-special-value is not really necessary. Sort will work just fine. But the volatile RAND() expression will cause B1:B40 to be recalculated when Sort writes back the sorted values. So B1:B40 will no longer reflect the order in A1:A40. No harm done. But it might be mystifying to the unwary user.) |
unique name list
You may also consider installing the MOREFUNC add-in and use its: MRAND
Function - as shown in the picture. http://img17.imageshack.us/img17/8778/nonameuy.png *** MRAND will assure NO repetitions *** http://www.download.com/Morefunc/300...-10423159.html Micky "CJ" wrote: Excel 2003. I have a list of 40 names. I would like to randomly select names without repeat until all 40 names have been selected. Any ideas please. |
unique name list
"××××× (××ק×) ×××××" <micky-a*at*tapuz.co.il wrote:
Although the chance of repetition two identical RAND() results is like winning the Lotto - can one assure it won't happen ? Not exactly. KB 828795 claims to describe the algorithm and constants used by RAND() (before XL2010). The claim is: "more than 10^13 numbers will be generated before the repetition". I presume that "more than 10^13" is exactly 27,814,431,486,576 (about 2.78E+13), which is 30268*30306*30322. So I guess one's "assurance" depends on your confidence in the correctness of KBs in general, or at least KB 828795 in particular. From my experience with other KBs, I must say that my confidence is less than 100%. Many of the KBs that I have read about the numerical properties of Excel algorithms contain technical errors, often material errors. I can say with impunity that __if__ the algorithm and constants in KB 828795 are correct, each modulo expression for IX, IY and IZ runs through its respective maximum sequence, not including zero, as long as Excel ensures that IX, IY and IZ are not zero when the algorithm is seeded. Thus, the algorithm will indeed produce about 2.78E+13 combinations of IX, IY and IZ. I cannot say with impunity that when those factors are combined to produce the pseudo-random number -- presumably IX/30269.0 + IY/30307.0 + IZ/30323.0 modulo 1 -- that produces 2.78E+13 unique floating point values, especially taking the limits of IEEE 64-bit floating point representation and perhaps Intel 80-bit floating point computation into account. I believe that can be proved only by producing and sorting all 2.78E+13 pseudo-random numbers, then comparing them pairwise. I think that would be computationally prohibitive on a single computer with an Intel CPU -- at least, mine. I did attempt to vet the algorithm and constants described in KB 828795 -- that is, to prove or disprove that they are correct. I was not successful. My method was to generate a pseudo-random number with RAND() -- actually several -- and try to reverse-engineer the factors IX, IY and IZ assuming the constants 30269, 30307 and 30323 and the Wichman-Hill algorithm described in KB 828795. I came very close; close enough to think that KB 828795 might be correct, but different enough to suspect that it might not be. The difference in binary representations was large enough that I was unable to explain it based on the numerical properties of alternative floating point methods. As an aside, I suspect that the algorithm described in a wiki article -- http://en.wikibooks.org/wiki/Statist...erics_in_Excel -- is incorrect __if__ KB 828795 is correct about generating "more than 10^13" unique pseudo-random numbers. (Admittedly, that's a big "if".) The wiki algorithm will not generate all 2.78E+13 combinations of IX, IY and IZ. Each modulo expression will produce zero; and once that happens, the modulo expression is stuck at zero. Of course, if/when that happens, an implementation could self-correct by reseeding the PRNG. But depending on the seed, the cycle can be very short. So I think the wiki algorithm is unlikely. Every other description of the Wichman-Hill algorithm is similar to KB 828795, at least functionally, if not in syntax. (I hope my comments do not spark a diatribe about the weaknesses of the Wichman-Hill(1982) algorithm. The weakness are well-known. That is why XL2010 abandoned it.) ----- original message ----- "××××× (××ק×) ×××××" <micky-a*at*tapuz.co.il wrote in message ... Although the chance of repetition two identical RAND() results is like winning the Lotto - can one assure it won't happen ? Micky "Joe User" wrote: "CJ" wrote: Excel 2003. I have a list of 40 names. I would like to randomly select names without repeat until all 40 names have been selected. One way.... Suppose the 40 names are in A1:A40. Put =RAND() into B1:B40. Then put the following formula into C1 and copy down through C40: =INDEX($A$1:$A$40,RANK(B1,$B$1:$B$40)) If you do not want B1:B40 changing every time a cell is edited, see my response in your thread "volatile v non-volatile". Alternatively, if you are doing this one time or infrequently, set up B1:B40 as above. If you use the volatile RAND() function, you might want to copy B1:B40 and paste-special-value over B1:B40. Then select A1:B40 and use Data Sort to sort column B. (The paste-special-value is not really necessary. Sort will work just fine. But the volatile RAND() expression will cause B1:B40 to be recalculated when Sort writes back the sorted values. So B1:B40 will no longer reflect the order in A1:A40. No harm done. But it might be mystifying to the unwary user.) |
unique name list
I wrote:
Every other description of the Wichman-Hill algorithm is similar to KB 828795, at least functionally, if not in syntax. I meant to write "every other description of the Wichman-Hill(1982) algorithm". I added 1982 to the last reference, but not to this one. ----- original message ----- "Joe User" <joeu2004 wrote in message ... "××××× (××ק×) ×××××" <micky-a*at*tapuz.co.il wrote: Although the chance of repetition two identical RAND() results is like winning the Lotto - can one assure it won't happen ? Not exactly. KB 828795 claims to describe the algorithm and constants used by RAND() (before XL2010). The claim is: "more than 10^13 numbers will be generated before the repetition". I presume that "more than 10^13" is exactly 27,814,431,486,576 (about 2.78E+13), which is 30268*30306*30322. So I guess one's "assurance" depends on your confidence in the correctness of KBs in general, or at least KB 828795 in particular. From my experience with other KBs, I must say that my confidence is less than 100%. Many of the KBs that I have read about the numerical properties of Excel algorithms contain technical errors, often material errors. I can say with impunity that __if__ the algorithm and constants in KB 828795 are correct, each modulo expression for IX, IY and IZ runs through its respective maximum sequence, not including zero, as long as Excel ensures that IX, IY and IZ are not zero when the algorithm is seeded. Thus, the algorithm will indeed produce about 2.78E+13 combinations of IX, IY and IZ. I cannot say with impunity that when those factors are combined to produce the pseudo-random number -- presumably IX/30269.0 + IY/30307.0 + IZ/30323.0 modulo 1 -- that produces 2.78E+13 unique floating point values, especially taking the limits of IEEE 64-bit floating point representation and perhaps Intel 80-bit floating point computation into account. I believe that can be proved only by producing and sorting all 2.78E+13 pseudo-random numbers, then comparing them pairwise. I think that would be computationally prohibitive on a single computer with an Intel CPU -- at least, mine. I did attempt to vet the algorithm and constants described in KB 828795 -- that is, to prove or disprove that they are correct. I was not successful. My method was to generate a pseudo-random number with RAND() -- actually several -- and try to reverse-engineer the factors IX, IY and IZ assuming the constants 30269, 30307 and 30323 and the Wichman-Hill algorithm described in KB 828795. I came very close; close enough to think that KB 828795 might be correct, but different enough to suspect that it might not be. The difference in binary representations was large enough that I was unable to explain it based on the numerical properties of alternative floating point methods. As an aside, I suspect that the algorithm described in a wiki article -- http://en.wikibooks.org/wiki/Statist...erics_in_Excel -- is incorrect __if__ KB 828795 is correct about generating "more than 10^13" unique pseudo-random numbers. (Admittedly, that's a big "if".) The wiki algorithm will not generate all 2.78E+13 combinations of IX, IY and IZ. Each modulo expression will produce zero; and once that happens, the modulo expression is stuck at zero. Of course, if/when that happens, an implementation could self-correct by reseeding the PRNG. But depending on the seed, the cycle can be very short. So I think the wiki algorithm is unlikely. Every other description of the Wichman-Hill algorithm is similar to KB 828795, at least functionally, if not in syntax. (I hope my comments do not spark a diatribe about the weaknesses of the Wichman-Hill(1982) algorithm. The weakness are well-known. That is why XL2010 abandoned it.) ----- original message ----- "××××× (××ק×) ×××××" <micky-a*at*tapuz.co.il wrote in message ... Although the chance of repetition two identical RAND() results is like winning the Lotto - can one assure it won't happen ? Micky "Joe User" wrote: "CJ" wrote: Excel 2003. I have a list of 40 names. I would like to randomly select names without repeat until all 40 names have been selected. One way.... Suppose the 40 names are in A1:A40. Put =RAND() into B1:B40. Then put the following formula into C1 and copy down through C40: =INDEX($A$1:$A$40,RANK(B1,$B$1:$B$40)) If you do not want B1:B40 changing every time a cell is edited, see my response in your thread "volatile v non-volatile". Alternatively, if you are doing this one time or infrequently, set up B1:B40 as above. If you use the volatile RAND() function, you might want to copy B1:B40 and paste-special-value over B1:B40. Then select A1:B40 and use Data Sort to sort column B. (The paste-special-value is not really necessary. Sort will work just fine. But the volatile RAND() expression will cause B1:B40 to be recalculated when Sort writes back the sorted values. So B1:B40 will no longer reflect the order in A1:A40. No harm done. But it might be mystifying to the unwary user.) |
unique name list
Hi CJ,
You seem to have several solution offers. Would you be interested in a modified BINGO number caller to pick your 40 names. A bingo caller selects a number between 1 and 75, divided between five column and posts it where ever the code dictates. It avoids duplicate picks by use of code and if all numbers are picked (very rare in bingo but would be every case in you project) it gives a message box to such. So you could have your 40 names (instead of 75 numbers) in a single column, in two columns or four columns. Then with a click of a button you could either click 40 times to bring up the random names, listed wherever you want, or add loop to the code to make forty picks on a single button click to do the same. Regards, Howard "CJ" wrote in message ... Excel 2003. I have a list of 40 names. I would like to randomly select names without repeat until all 40 names have been selected. Any ideas please. |
| All times are GMT +1. The time now is 04:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com