Hi, I am done. After 4 hours I cannot find an easy method to create 1000
rows with each row having 5 values. These value are unique and between 1
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 of
brain.

thanks in advance.


--
hasinque
------------------------------------------------------------------------
hasinque's Profile: http://www.excelforum.com/member.php...o&userid=35387
View this thread: http://www.excelforum.com/showthread...hreadid=551625

Here is one way

Goto ToolsOptions 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(A10,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" wrote in
message ...

Hi, I am done. After 4 hours I cannot find an easy method to create 1000
rows with each row having 5 values. These value are unique and between 1
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 of
brain.

thanks in advance.


--
hasinque
------------------------------------------------------------------------
hasinque's Profile:
http://www.excelforum.com/member.php...o&userid=35387
View this thread: http://www.excelforum.com/showthread...hreadid=551625

many thanks.

however the formula is not working, it gives an error where it tests
whether A10.


--
hasinque
------------------------------------------------------------------------
hasinque's Profile: http://www.excelforum.com/member.php...o&userid=35387
View this thread: http://www.excelforum.com/showthread...hreadid=551625

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

It works fine. Did you check the Iteration box?

--

HTH

Bob Phillips

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

"hasinque" wrote in
message ...

many thanks.

however the formula is not working, it gives an error where it tests
whether A10.


--
hasinque
------------------------------------------------------------------------
hasinque's Profile:
http://www.excelforum.com/member.php...o&userid=35387
View this thread: http://www.excelforum.com/showthread...hreadid=551625