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.

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.

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.

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" wrote in message
...
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.

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" wrote in message
...
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.

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" wrote in message
...
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.

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