Random Numbers

[Leon]

I need a formula/macro to display a set of 8 pairs of random numbers.
Each number in a pair cannot be equal to the other, numbers in a pair can be
equal to the ones in an other pair.
I tried Randbetween(a,b) but i get a lot of invalid(equal) pairs.

Any help will be gratefully appreciated.

[Tom Ogilvy]

What are the range of numbers from which you are choosing?

1 to 10?

20 to 77 ?

--
Regards,
Tom Ogilvy



"Leon" wrote:

> I need a formula/macro to display a set of 8 pairs of random numbers.
> Each number in a pair cannot be equal to the other, numbers in a pair can be
> equal to the ones in an other pair.
> I tried Randbetween(a,b) but i get a lot of invalid(equal) pairs.
>
> Any help will be gratefully appreciated.

[Gary''s Student]

Let's say you want the randbetween to be between 1 and 100.

1. enter the numbers 1 thru 100 in cells A1 thru A100
2. enter =RAND() in cells B1 thru B100
3. sort cols A&B by B
for the first pair select A1 & A2
for the second pair select A3 & A4
..
..
..
--
Gary's Student


"Leon" wrote:

> I need a formula/macro to display a set of 8 pairs of random numbers.
> Each number in a pair cannot be equal to the other, numbers in a pair can be
> equal to the ones in an other pair.
> I tried Randbetween(a,b) but i get a lot of invalid(equal) pairs.
>
> Any help will be gratefully appreciated.

[Bernie Deitrick]

Leon,

A simple way would be to list your valid pairs in columns A and B, then use the formula

=RAND()

in column C, matching your list in columns A and B. When you want a new set or random pairs, force
a calculation, and then sort based on column C, and take the top 8 pairs from Columns A and B.

HTH,
Bernie
MS Excel MVP


"Leon" <[email protected]> wrote in message
news:[email protected]...
>I need a formula/macro to display a set of 8 pairs of random numbers.
> Each number in a pair cannot be equal to the other, numbers in a pair can be
> equal to the ones in an other pair.
> I tried Randbetween(a,b) but i get a lot of invalid(equal) pairs.
>
> Any help will be gratefully appreciated.

[Leon]

Thanks, Tom, Gary & Bernie.
I need 8 pairs of rtandom numbers from 0 to 4
I guess Bernie's solution is the lightest to implement once I write all the
possible pairs. Which I guess amount to 20.

Thanks to all


"Bernie Deitrick" wrote:

> Leon,
>
> A simple way would be to list your valid pairs in columns A and B, then use the formula
>
> =RAND()
>
> in column C, matching your list in columns A and B. When you want a new set or random pairs, force
> a calculation, and then sort based on column C, and take the top 8 pairs from Columns A and B.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Leon" <[email protected]> wrote in message
> news:[email protected]...
> >I need a formula/macro to display a set of 8 pairs of random numbers.
> > Each number in a pair cannot be equal to the other, numbers in a pair can be
> > equal to the ones in an other pair.
> > I tried Randbetween(a,b) but i get a lot of invalid(equal) pairs.
> >
> > Any help will be gratefully appreciated.
>
>
>

[Tom Ogilvy]

Sub Writeallpairs()
Dim i As Long, j As Long, k As Long
Dim rng As Range
Range("A:C").ClearContents
j = 1
For i = 0 To 4
For k = 0 To 4
If i <> k Then
Cells(j, 1) = i: Cells(j, 2) = k
j = j + 1
End If
Next k, i
Set rng = Cells(1, 1).Resize(j - 1, 1)
rng.Offset(0, 2).Formula = "=rand()"
Range("A1").Resize(j - 1, 3).Sort Key1:=Range("C1")
Columns(3).ClearContents
Set rng = Range(Cells(9, 1), Cells(Rows.Count, 1).End(xlUp))
rng.EntireRow.Delete
End Sub


--
Regards,
Tom Ogilvy




"Leon" wrote:

> Thanks, Tom, Gary & Bernie.
> I need 8 pairs of rtandom numbers from 0 to 4
> I guess Bernie's solution is the lightest to implement once I write all the
> possible pairs. Which I guess amount to 20.
>
> Thanks to all
>
>
> "Bernie Deitrick" wrote:
>
> > Leon,
> >
> > A simple way would be to list your valid pairs in columns A and B, then use the formula
> >
> > =RAND()
> >
> > in column C, matching your list in columns A and B. When you want a new set or random pairs, force
> > a calculation, and then sort based on column C, and take the top 8 pairs from Columns A and B.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Leon" <[email protected]> wrote in message
> > news:[email protected]...
> > >I need a formula/macro to display a set of 8 pairs of random numbers.
> > > Each number in a pair cannot be equal to the other, numbers in a pair can be
> > > equal to the ones in an other pair.
> > > I tried Randbetween(a,b) but i get a lot of invalid(equal) pairs.
> > >
> > > Any help will be gratefully appreciated.
> >
> >
> >

[[email protected]]

Hello Leon,

Select two cells and enter
=UniqRandInt(5)-1
as array formula (CTRL + SHIFT + ENTER).

Enter my UDF into a module:
http://www.sulprobil.com/html/uniqrandint.html

HTH,
Bernd