Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Number Cell Filling
Hello Folks,
This is my first post in about 2½ years, it's so good to be back. In cells a1 to a20, I have 20 different numbers, obviously 1 number in each cell. In cells b1 to b20, I would like those 20 amounts, but placed in randomly. All help gratefully received. Your help is and always has been very much appreciated. Thanking you in anticipation. <-<-<-<- Big Rick |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Number Cell Filling
Try this in B1 and copy down to B20
=INDIRECT("A"&ROUND(RAND()*20,0)) "Big Rick" wrote: Hello Folks, This is my first post in about 2½ years, it's so good to be back. In cells a1 to a20, I have 20 different numbers, obviously 1 number in each cell. In cells b1 to b20, I would like those 20 amounts, but placed in randomly. All help gratefully received. Your help is and always has been very much appreciated. Thanking you in anticipation. <-<-<-<- Big Rick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Number Cell Filling
Hi Sheeloo
Many thanks for you reply. This does work, but only to a small degree for my needs. What I should of pointed out is that I needed all 20 numbers in col A to be replicated in random order in col B. The formula also gives gives me random REF# errors in some of the cells. I cannot figure out why. Perhaps you could help me a little bit further. <-<-<-<- Big Rick "Sheeloo" wrote: Try this in B1 and copy down to B20 =INDIRECT("A"&ROUND(RAND()*20,0)) "Big Rick" wrote: Hello Folks, This is my first post in about 2½ years, it's so good to be back. In cells a1 to a20, I have 20 different numbers, obviously 1 number in each cell. In cells b1 to b20, I would like those 20 amounts, but placed in randomly. All help gratefully received. Your help is and always has been very much appreciated. Thanking you in anticipation. <-<-<-<- Big Rick |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Number Cell Filling
Big wrote on Sat, 27 Jun 2009 07:31:01 -0700:
This does work, but only to a small degree for my needs. What I should of pointed out is that I needed all 20 numbers in col A to be replicated in random order in col B. The formula also gives gives me random REF# errors in some of the cells. I cannot figure out why. Perhaps you could help me a little bit further. <-<-<-<- Big Rick "Sheeloo" wrote: Try this in B1 and copy down to B20 =INDIRECT("A"&ROUND(RAND()*20,0)) "Big Rick" wrote: Hello Folks, This is my first post in about 2½ years, it's so good to be back. In cells a1 to a20, I have 20 different numbers, obviously 1 number in each cell. In cells b1 to b20, I would like those 20 amounts, but placed in randomly. All help gratefully received. Your help is and always has been very much appreciated. Thanking you in anticipation. <-<-<-<- Big Rick Can I put in a few thoughts? The formula could use RANDBETWEEN(1,20) instead of rounding. Another problem is that you may get identical numbers in column B. The traditional method is to have a helper column with random numbers and sort on that. It's still possible but unlikely that the same value will arise but you could use two helper columns with 1:20 in B say and RAND() in C, then sorting on C and B and using the INDIRECT referring to B will give really random values in D. As a bonus, this last would preserve the A column. Thank you Sheeloo for reminding me of the ingenious use of INDIRECT. I'd forgotten about it. -- James Silverton Potomac, Maryland Email, with obvious alterations: not.jim.silverton.at.verizon.not |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Number Cell Filling
Hi,
1. Highlight A1:A20 and drag the fill handle to the right on column 2. In C1 enter =RAND() or =RANDBETWEEN(1,20) it really makes no difference 3. Fill this down to C20 4. Highlight B1:C20 and sort on C. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Big Rick" wrote: Hi Sheeloo Many thanks for you reply. This does work, but only to a small degree for my needs. What I should of pointed out is that I needed all 20 numbers in col A to be replicated in random order in col B. The formula also gives gives me random REF# errors in some of the cells. I cannot figure out why. Perhaps you could help me a little bit further. <-<-<-<- Big Rick "Sheeloo" wrote: Try this in B1 and copy down to B20 =INDIRECT("A"&ROUND(RAND()*20,0)) "Big Rick" wrote: Hello Folks, This is my first post in about 2½ years, it's so good to be back. In cells a1 to a20, I have 20 different numbers, obviously 1 number in each cell. In cells b1 to b20, I would like those 20 amounts, but placed in randomly. All help gratefully received. Your help is and always has been very much appreciated. Thanking you in anticipation. <-<-<-<- Big Rick |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Number Cell Filling
What you're looking for is a random order without replacement (no
duplicates). In an out-of-the-way location, say Z1 to Z20, enter the Rand function: In Z1 enter: =Rand() and copy down to Z20. With your original numbers in A1 to A20, in B1 enter: =INDEX(A$1:A$20,RANK(Z1,Z$1:Z$20)) And copy down. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Big Rick" wrote in message ... Hi Sheeloo Many thanks for you reply. This does work, but only to a small degree for my needs. What I should of pointed out is that I needed all 20 numbers in col A to be replicated in random order in col B. The formula also gives gives me random REF# errors in some of the cells. I cannot figure out why. Perhaps you could help me a little bit further. <-<-<-<- Big Rick "Sheeloo" wrote: Try this in B1 and copy down to B20 =INDIRECT("A"&ROUND(RAND()*20,0)) "Big Rick" wrote: Hello Folks, This is my first post in about 2½ years, it's so good to be back. In cells a1 to a20, I have 20 different numbers, obviously 1 number in each cell. In cells b1 to b20, I would like those 20 amounts, but placed in randomly. All help gratefully received. Your help is and always has been very much appreciated. Thanking you in anticipation. <-<-<-<- Big Rick |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Number Cell Filling
Should mention that each time you hit <F9, you'll get a *new* random list.
-- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... What you're looking for is a random order without replacement (no duplicates). In an out-of-the-way location, say Z1 to Z20, enter the Rand function: In Z1 enter: =Rand() and copy down to Z20. With your original numbers in A1 to A20, in B1 enter: =INDEX(A$1:A$20,RANK(Z1,Z$1:Z$20)) And copy down. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Big Rick" wrote in message ... Hi Sheeloo Many thanks for you reply. This does work, but only to a small degree for my needs. What I should of pointed out is that I needed all 20 numbers in col A to be replicated in random order in col B. The formula also gives gives me random REF# errors in some of the cells. I cannot figure out why. Perhaps you could help me a little bit further. <-<-<-<- Big Rick "Sheeloo" wrote: Try this in B1 and copy down to B20 =INDIRECT("A"&ROUND(RAND()*20,0)) "Big Rick" wrote: Hello Folks, This is my first post in about 2½ years, it's so good to be back. In cells a1 to a20, I have 20 different numbers, obviously 1 number in each cell. In cells b1 to b20, I would like those 20 amounts, but placed in randomly. All help gratefully received. Your help is and always has been very much appreciated. Thanking you in anticipation. <-<-<-<- Big Rick |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Number Cell Filling
Hello,
You can use my UDF Random_Pick: http://www.sulprobil.com/html/uniqrandint.html Regards, Bernd |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Number Cell Filling
Hi RagDyer
Yours was the one that worked best and was the simplest for me, so have used your formula. I remember you from the 'olden days'! Many thanks to you. My many thanks also to Shane, James, and Sheeloo for your help. It is very much appreciated from all of you. Best regards <-<-<-<- Big Rick "RagDyeR" wrote: Should mention that each time you hit <F9, you'll get a *new* random list. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... What you're looking for is a random order without replacement (no duplicates). In an out-of-the-way location, say Z1 to Z20, enter the Rand function: In Z1 enter: =Rand() and copy down to Z20. With your original numbers in A1 to A20, in B1 enter: =INDEX(A$1:A$20,RANK(Z1,Z$1:Z$20)) And copy down. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Big Rick" wrote in message ... Hi Sheeloo Many thanks for you reply. This does work, but only to a small degree for my needs. What I should of pointed out is that I needed all 20 numbers in col A to be replicated in random order in col B. The formula also gives gives me random REF# errors in some of the cells. I cannot figure out why. Perhaps you could help me a little bit further. <-<-<-<- Big Rick "Sheeloo" wrote: Try this in B1 and copy down to B20 =INDIRECT("A"&ROUND(RAND()*20,0)) "Big Rick" wrote: Hello Folks, This is my first post in about 2½ years, it's so good to be back. In cells a1 to a20, I have 20 different numbers, obviously 1 number in each cell. In cells b1 to b20, I would like those 20 amounts, but placed in randomly. All help gratefully received. Your help is and always has been very much appreciated. Thanking you in anticipation. <-<-<-<- Big Rick |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Number Cell Filling
You're making me feel old!<bg
Glad to help and appreciate the feed-back. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Big Rick" wrote in message ... Hi RagDyer Yours was the one that worked best and was the simplest for me, so have used your formula. I remember you from the 'olden days'! Many thanks to you. My many thanks also to Shane, James, and Sheeloo for your help. It is very much appreciated from all of you. Best regards <-<-<-<- Big Rick "RagDyeR" wrote: Should mention that each time you hit <F9, you'll get a *new* random list. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... What you're looking for is a random order without replacement (no duplicates). In an out-of-the-way location, say Z1 to Z20, enter the Rand function: In Z1 enter: =Rand() and copy down to Z20. With your original numbers in A1 to A20, in B1 enter: =INDEX(A$1:A$20,RANK(Z1,Z$1:Z$20)) And copy down. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Big Rick" wrote in message ... Hi Sheeloo Many thanks for you reply. This does work, but only to a small degree for my needs. What I should of pointed out is that I needed all 20 numbers in col A to be replicated in random order in col B. The formula also gives gives me random REF# errors in some of the cells. I cannot figure out why. Perhaps you could help me a little bit further. <-<-<-<- Big Rick "Sheeloo" wrote: Try this in B1 and copy down to B20 =INDIRECT("A"&ROUND(RAND()*20,0)) "Big Rick" wrote: Hello Folks, This is my first post in about 2½ years, it's so good to be back. In cells a1 to a20, I have 20 different numbers, obviously 1 number in each cell. In cells b1 to b20, I would like those 20 amounts, but placed in randomly. All help gratefully received. Your help is and always has been very much appreciated. Thanking you in anticipation. <-<-<-<- Big Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
random number in a cell | Excel Discussion (Misc queries) | |||
Random cell/number and dont repeat selected cell/number | Excel Discussion (Misc queries) | |||
How can I match a random number with closest number from sequence? | Excel Worksheet Functions | |||
filling information from one cell and filling another. | Excel Worksheet Functions | |||
How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) |