Need formula for :

  • Thread starter Thread starter The Wraith
  • Start date Start date
T

The Wraith

I have formula wich generate a random number from a list ..

-How can i let this formula runs 60 Times , and then put each rando
number in seperated boxes...

need 10 boxes with each 6 different numbers..non of 6 numbers in a bo
may be the same..

It would be a great help thank you..

All info about usefull formulas are welcome ?

Thx guys.
 
Wraith,

I'm not sure of what you exactly want, but if you want the same formula
applied 60 times, put it in 60 cells.
To make that a bit easier : Select 60 cells ( for instance 10 rows and 6
columns to "look like" your boxes) , enter the formula which you apparently
allready have in the active cell and confirm with <CNTRL>+<ENTER>.
That places the formula in one sweep in all 60 cells.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Ok
i could put this formula in the 60 boxes ...

But with wich formula i can see if non of the 6 boxes with
random numbers has the same number...

(Just info then about :how can i let a Formula calculate more time
when i run just 1 time my sheet)

Thx.
 
Hi T W,

If I understand you correctly, try this.
A1:A60 = 1 through 60
B1:B60 = =RAND()

Paste this code in the sheet module.
Returns 6 random numbers in 10 columns.

Sub RandSixBoxes()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Data As Range

Set Data = Range("A1:B60")
k = 1
j = 5

Data.Sort Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

For i = 1 To 10
Range("A65").End(xlUp).Offset(-j, 0). _
Resize(6, 1).Copy Range("B1").Offset(0, k)
j = j + 6
k = k + 1
Next
End Sub

HTH
Regards,
Howard
 
T.W,

I'm not sure what you want exactly.
I now understand that you want 60 unique random numbers and allready have
the formula with which you calculate random numbers (but possible dubble).
If so : I think the best approach is to calculate a column of *more* than 60
random numbers (lets say A1:A100) , then check the numbers to see whether
they are unique (see below) and "pick" the first 60 numbers which are and
use them for your needs.

If you have your random numbers in A1:A100, put the formula
=IF(COUNTIF($A$1:A1,A1)=1,A1,"") (whick I found on
http://www.cpearson.com/excel/duplicat.htm) in cell B1 and copy down to
B100.
NB Note that the first time cell A1 is referenced it's $A$1 (absolute
reference) but all the other references are relative. This is necessary for
the formula to work correct if it's copied down! The $A$1 stays unchanged
by the copy while the A1 will change to A2, A3 etc.
This formula will accept the first occurence of any random number, but will
replace the second or later occurence with ""

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Ok thx very much

I gona try those allready..
when i'm close to the finish with this i give a sign..

good sugestions allways welcome..


So..

---i've got 50 numbers fix in a list..
---my random formula picks a random number out that list...
-- this number goes to a box...
--- but i don't know how to let it count 60 times and put next
random in other box..

now i search for..

---formula wich can give me 60 times a random number out my list
--- each number must be put seperately in a box
---need : 10 rows of 6 boxes with my randoms in it
---non of the randoms each row of 6 may be the same ..

so any formula or idee for this is welcome..
 
I think you need to explain a bit more:

how do you expect to get 60 non-repeating numbers when you start with
only 50 numbers in your list?

If your numbers are continuous, you might instead want to look at the
RANDINT() function at

http://www.mcgimpsey.com/excel/randint.html


Select your 10 x 6 range of cells, then array-enter

=RANDINT()

to get numbers from 1 to 60, randomly ordered. Or specify a small and
large value.
 
Hi Guys..

Ok some formulas are working good...good job..thx..
i'm still in experimental phase..

Now i need to find out ...:

--1-- I gave 60 boxes the same (random) formule..
--2-- Those 60 random numbers go to 60 seperated boxes
10 rows of 6 numbers
--3-- I used modul to arange the 6 number in the 10 rows
from high to low ...

but some times i have the same numbers in the rows of my 6 number
!!!!!! how can i fix this ..?

How can i put a part extra to my 2nd random formula (list of 60)

so how can box2 see if box1 isn't the same numbers ..??
and then give other random number..

and the 3rd looking at the 2nd
and the 4th looking at the 3th etc...etc... each 6 randoms

is this possible.. (only in groups of 6 randoms) for each row..
1-6 then 7-12 next 12-17 etc...


thx ...
Finish is coming closer..
 
Back
Top