Filter unique random number

[T Harris]

Using 75 cells down begining with A1 and B1, I have random number between
1-75 in B column and corresponding letters of the word BINGO to appear
accordingly in the A column. Thanks to three people in this forum, this is
working. Now when I hit F9, the BINGO results are displayed in the first
75 rows of the A and B columns.

Now, how do I filter the 75 results to weed out duplicates like "B12"? (OR
another solution which may be the best is) How do I generate the 75 unique
solutions so that no duplicates appear in the first place? If anyone has an
answer to either question, I would appreciate it very much. I tried
ADVANCED FILTER and could not get it to work and output my unique results to
another location. Thanks.

T Harris

 

[Guest]

Using 75 cells down begining with A1 and B1,
I have random number between 1-75 in B column
and corresponding letters of the word BINGO to
appear accordingly in the A column. [....] Now
when I hit F9, the BINGO results are displayed in
the first 75 rows of the A and B columns.
[....] How do I generate the 75 unique solutions
so that no duplicates appear in the first place?

How automatic do you want this to be? One
approach, following McGimpsey's suggestion
with an important correction:

A1: =MID("BINGO", INT((B1-1)/15)+1, 1)
A2: copy A1
B1: 1
B2: 2
C1: =RAND()
C2: copy C2

Copy A1:C2 (2 rows, 3 columns) down thru C75.
Note that B will contain constants 1-75.

Use Data > Sort to sort A1:C75 based on C1.
Repeat this sort each time after you press F9.

 

[Guest]

Now when I hit F9, the BINGO results are displayed
in the first 75 rows of the A and B columns.

[....]
Use Data > Sort to sort A1:C75 based on C1.
Repeat this sort each time after you press F9.

Since you are using F9, I ass-u-me-d you selected
manual calculation under Tools > Options > Calculation.

If not (if you still use automatic calculation), you can
simply use Data > Sort instead of pressing F9. The
order of C1 will look odd, but A1:B75 will be ordered
randomly, properly paired.

 

[Max]

.. How do I generate the 75 unique solutions

so that no duplicates appear in the first place?

One play to try ..

Sample construct available at:
http://www.savefile.com/files/3005009
FilterUniqueRandom_THarris_wks.xls

Put in

A1:
=INDEX(B:B,RANK(D1,$D$1:$D$75))&INDEX(C:C,RANK(D1,$D$1:$D$75))

B1:
=VLOOKUP(INT((ROW(A1)-1)/15)+1,{1,"B";2,"I";3,"N";4,"G";5,"O"},2,0)

C1: =ROW(A1)
D1: =RAND()

Select A11, copy down to D75

A1:A75 will return a unique random shuffle of all the 75 alpha-numbers
(B1 - O75). Pressing F9 will regenerate a fresh shuffle.

You might also be interested in this "Bingo Board" program file
which was put together (based on code from Tom Ogilvy)
and posted a few months back:

Link: http://savefile.com/files/5028169
Bingo_Board_v2_Random_Draw_without_Replacement.xls
(Its easy and fun to use !)


--

 

[T Harris]

Thanks to everyone. Problem solved.

 

[Max]

You're welcome, T Harris !