Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique random number in excel
I am using excel to make bingo cards. I am using the =randbetween(*,*)
formula to generate the numbers but I keep getting duplicate numbers. How can I ensure that each cell will have a unique number? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique random number in excel
On Apr 3, 6:59 pm, SeanW. wrote:
I am using excel to make bingo cards. I am using the =randbetween(*,*) formula to generate the numbers but I keep getting duplicate numbers. How can I ensure that each cell will have a unique number? 1) In Column A, enter in the range of numbers you need eg. 1 through 25 2) In column B, enter =rand() and fill for as many rows as you have values in column A 3) Hit F9 (calculate) 4) Select column B, copy, paste-special values only. 5) Sort by column B, now you have the values you want (column A) in a random order and without dups. Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique random number in excel
I tried that and it really didn't do what I need. What I need is five
columns, five rows each, of unique random numbers from a set range, i.e. column A = 1 - 15 column B = 16 - 30 column C = 31 - 45 and so on, without duplicating any numbers in any column. "cht13er" wrote: On Apr 3, 6:59 pm, SeanW. wrote: I am using excel to make bingo cards. I am using the =randbetween(*,*) formula to generate the numbers but I keep getting duplicate numbers. How can I ensure that each cell will have a unique number? 1) In Column A, enter in the range of numbers you need eg. 1 through 25 2) In column B, enter =rand() and fill for as many rows as you have values in column A 3) Hit F9 (calculate) 4) Select column B, copy, paste-special values only. 5) Sort by column B, now you have the values you want (column A) in a random order and without dups. Chris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique random number in excel
Request my Bingo Cards workbook and sneak a look at the code. VBE project password is provided with the workbook. I use a two array technique that Tom Ogilvy posted quite a while back. Info at my website... http://www.realezsites.com/bus/primitivesoftware (at the bottom of the products page) -- Jim Cone San Francisco, USA (Excel Add-ins / Excel Programming) "SeanW." wrote in message ... I am using excel to make bingo cards. I am using the =randbetween(*,*) formula to generate the numbers but I keep getting duplicate numbers. How can I ensure that each cell will have a unique number? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique random number in excel
Most important part... It's free. '-- Jim Cone Request my Bingo Cards workbook and sneak a look at the code. VBE project password is provided with the workbook. I use a two array technique that Tom Ogilvy posted quite a while back. Info at my website... http://www.realezsites.com/bus/primitivesoftware (at the bottom of the products page) -- Jim Cone San Francisco, USA (Excel Add-ins / Excel Programming) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique random number in excel
On Apr 3, 7:47 pm, SeanW. wrote:
I tried that and it really didn't do what I need. What I need is five columns, five rows each, of unique random numbers from a set range, i.e. column A = 1 - 15 column B = 16 - 30 column C = 31 - 45 and so on, without duplicating any numbers in any column. "cht13er" wrote: On Apr 3, 6:59 pm, SeanW. wrote: I am using excel to make bingo cards. I am using the =randbetween(*,*) formula to generate the numbers but I keep getting duplicate numbers. How can I ensure that each cell will have a unique number? 1) In Column A, enter in the range of numbers you need eg. 1 through 25 2) In column B, enter =rand() and fill for as many rows as you have values in column A 3) Hit F9 (calculate) 4) Select column B, copy, paste-special values only. 5) Sort by column B, now you have the values you want (column A) in a random order and without dups. Chris "I tried that and it really didn't do what I need. What I need is five columns, five rows each, of unique random numbers from a set range, i.e. column A = 1 - 15 column B = 16 - 30 column C = 31 - 45 " You could just simply repeat the steps I gave you five times ... or check out the other stuff Jim posted, whatever works for you :) Chris |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique random number in excel
In A1 thru A75 enter 1 thru 75
In B1 thru B75 enter =RAND() In C1 enter: =INDIRECT("A"&ROWS($A$1:A1)*5-7+COLUMN()) copy C1 from C1 thru G5 The 5 x 5 table of C1 thru G5 are the first 25 items in column A. Now sort columns A & B by column B to "shuffle" column A. This will produce a random, non-repeating bingo card in C1 thru G5: 31 0.873386313 31 64 67 2 44 64 0.190512371 35 5 28 69 3 67 0.450199278 4 7 6 70 13 2 0.150611996 59 57 14 47 25 44 0.699875147 20 71 51 54 15 35 0.590930193 5 0.800109683 28 0.768162 69 0.529879827 3 0.583946572 4 0.088521802 7 0.307539103 6 0.552854982 70 0.545292593 13 0.980841576 59 0.441444118 57 0.809513206 14 0.740227015 47 0.638968569 25 0.898811442 20 0.474192145 71 0.926925361 51 0.808214507 54 0.500026701 15 0.192091325 16 0.73617562 39 0.651063112 23 0.710989041 30 0.738307198 26 0.396567303 18 0.877376344 43 0.685148198 22 0.834695948 29 0.126316753 55 0.57929831 73 0.906039845 48 0.501519972 52 0.482801842 53 0.97355376 17 0.924399231 38 0.302151165 58 0.634008572 34 0.736748499 19 0.252384284 56 0.440522143 62 0.692009145 49 0.447947276 61 0.433138577 10 0.890916639 45 0.314376189 11 0.545736645 46 0.521647801 21 0.760234484 41 0.693943117 63 0.233324578 37 0.637923286 68 0.161107715 72 0.428644293 66 0.618431429 32 0.044156609 40 0.426681574 42 0.051775035 65 0.826390305 36 0.255918909 8 0.81744183 50 0.686003118 27 0.330331457 75 0.860733204 33 0.818331415 60 0.065291734 1 0.371738515 24 0.4384991 12 0.056443785 9 0.90363569 74 0.472120427 -- Gary''s Student - gsnu200777 "SeanW." wrote: I am using excel to make bingo cards. I am using the =randbetween(*,*) formula to generate the numbers but I keep getting duplicate numbers. How can I ensure that each cell will have a unique number? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique random number in excel
Hello,
Select A1:E1 and array-enter =Uniqrandint(15) Then select A2:E2 and array-enter =Uniqrandint(15)+15 Then A3:E3 and =Uniqrandint(15)+30 and so on. Uniqrandint you can find he http://www.sulprobil.com/html/uniqrandint.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Random Number Generator | Excel Discussion (Misc queries) | |||
Non updatable Unique Random Number | Excel Worksheet Functions | |||
Filter unique random number | Excel Worksheet Functions | |||
Showing a unique random number w/o duplicates | Excel Worksheet Functions | |||
How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) |