software

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 quot;B12quot;? (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
quot;T Harrisquot; wrote:
gt; Using 75 cells down begining with A1 and B1,
gt; I have random number between 1-75 in B column
gt; and corresponding letters of the word BINGO to
gt; appear accordingly in the A column. [....] Now
gt; when I hit F9, the BINGO results are displayed in
gt; the first 75 rows of the A and B columns.
gt; [....] How do I generate the 75 unique solutions
gt; 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(quot;BINGOquot;, 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 gt; Sort to sort A1:C75 based on C1.
Repeat this sort each time after you press F9.

I wrote:
gt; quot;T Harrisquot; wrote:
gt; gt; Now when I hit F9, the BINGO results are displayed
gt; gt; in the first 75 rows of the A and B columns.
gt; [....]
gt; Use Data gt; Sort to sort A1:C75 based on C1.
gt; 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 gt; Options gt; Calculation.

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

gt; .. How do I generate the 75 unique solutions
gt; so that no duplicates appear in the first place?

One play to try ..

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

Put in

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

B1:
=VLOOKUP(INT((ROW(A1)-1)/15) 1,{1,quot;Bquot;;2,quot;Iquot;;3,quot;Nquot;;4,quot;Gquot;;5,quot;Oquot;},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 quot;Bingo Boardquot; program file
which was put together (based on code from Tom Ogilvy)
and posted a few months back:

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

--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
quot;T Harrisquot; gt; wrote in message
...
gt; Using 75 cells down begining with A1 and B1, I have random number between
gt; 1-75 in B column and corresponding letters of the word BINGO to appear
gt; accordingly in the A column. Thanks to three people in this forum, this
is
gt; working. Now when I hit F9, the BINGO results are displayed in the first
gt; 75 rows of the A and B columns.
gt;
gt; Now, how do I filter the 75 results to weed out duplicates like quot;B12quot;?
(OR
gt; another solution which may be the best is) How do I generate the 75 unique
gt; solutions so that no duplicates appear in the first place? If anyone has
an
gt; answer to either question, I would appreciate it very much. I tried
gt; ADVANCED FILTER and could not get it to work and output my unique results
to
gt; another location. Thanks.
gt;
gt; T Harris
gt;
gt;
Thanks to everyone. Problem solved.quot;T Harrisquot; gt; wrote in message
...
gt; Using 75 cells down begining with A1 and B1, I have random number between
gt; 1-75 in B column and corresponding letters of the word BINGO to appear
gt; accordingly in the A column. Thanks to three people in this forum, this
gt; is working. Now when I hit F9, the BINGO results are displayed in the
gt; first 75 rows of the A and B columns.
gt;
gt; Now, how do I filter the 75 results to weed out duplicates like quot;B12quot;?
gt; (OR another solution which may be the best is) How do I generate the 75
gt; unique solutions so that no duplicates appear in the first place? If
gt; anyone has an answer to either question, I would appreciate it very much.
gt; I tried ADVANCED FILTER and could not get it to work and output my unique
gt; results to another location. Thanks.
gt;
gt; T Harris
gt;
You're welcome, T Harris !
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
quot;T Harrisquot; gt; wrote in message
...
gt; Thanks to everyone. Problem solved.