Random numbers (with a twist)

[Pat McGuirk]

I need to generate random numbers (1-15) In 3 different
columns. (Each column will have the numbers 1-15, and no
number is repeated in the column).

Here's the twist... In those three columns, no row can
have the same number. Again, each row will randomly enter
the numbers 1-15 (repeated in rows A, B & C). But no row
can have the same number in it.

Any suggestions??? Thanks in Advance!!

- Pat

 

[Max]

Try playing around with this set-up

Put in A1: =RAND()
copy down A1:A15, then copy across A1:C15

Name the range A1:A15 as say: TBL1
Name the range B1:B15 as say: TBL2
Name the range C1:C15 as say: TBL3

Select D115
Put in the formula bar: =RANK(TBL1,TBL1)
Hold down Ctrl + Shift keys, press Enter
(It's an array formula)

Done correctly, Excel will wrap curly braces around
the formula, viz: {=RANK(TBL1,TBL1)}

In D115 will be a random shuffle of the numbers 1 - 15
without repeats.

Repeat steps for D115 to set up in E1:E15 and in F1:F15, viz:

for E1:E15 - use: =RANK(TBL2,TBL2)
for F1:F15 - use: =RANK(TBL3,TBL3)

using Ctrl+Shift+Enter to enter the array formulas each time

Put in G1: =IF(OR(D1=E1,D1=F1,E1=F1),"X","")
copy down G1:G15
(this checks and alerts if there are any row repeats in D1:F15)

Put in G16: =IF(COUNTIF(G1:G15,"X")<>0,"","STOP")
(this gives a visual when to stop the recalculation)

Tap / press F9 to generate a new random splash in D1:F15

Continue tapping F9 to recalculate until G16 returns "STOP"

When this happens, you will have what you want in D1:F15