unique random number in excel

[SeanW.]

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?

 

[cht13er]

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

 

[SeanW.]

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.

 

[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)


"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?

 

[Jim Cone]

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)

 

[cht13er]

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.

"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

 

[Gary''s Student]

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

 

[Bernd P]

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 here:
http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd