Create 5 unique value between 1 and 14?

[hasinque]

Hi, I am done. After 4 hours I cannot find an easy method to create 100
rows with each row having 5 values. These value are unique and between
and 14.

So it looks like this
3 4 6 10 12
11 12 1 2 5
.........

please, there must be an easy solution. dont hate me for the lack o
brain.

thanks in advance

 

[Bob Phillips]

Here is one way

Goto Tools>Options and on the Calculation tab click the Iteration checkbox
(this will suppress circular reference messages)


Put this formula in A1, and copy across to E1. AT this stage they will all
show 0.

Copy A1:E1 down to however many rows.

=IF(($M$1="")+(AND(A1>0,COUNTIF($A1:$E1,A1)=1)),A1,INT(RAND()*14+1))

Then enter some value in M1, say x, and the random numbers will generate.

To force a re-calculation, clear cell M1, edit cell A1, don't change it,
just edit to reset to 0, copy A1 acrfoss to E1 and down, and re-enter a
value in M1.


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[hasinque]

many thanks.

however the formula is not working, it gives an error where it tests
whether A1>0.

 

[bplumhoff]

Hello,

I suggest to:

1. Take my UDF UniqRandInt from
http://www.sulprobil.com/html/uniqrandint.html
Press ALT + F11, insert a module and copy my macro source code into
your module.

2. Select the 5 cells in your first row you want to fill and enter:
=UniqRandInt(14)
as array formula (enter with CTRL + SHIFT + ENTER)
The formula should be indentical in all your 5 cells and curly brackets
should appear.

3. Copy the row down as far as necessary.

HTH,
Bernd

 

[Bob Phillips]

It works fine. Did you check the Iteration box?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)