excel - Generate 5000 records in 2 columns of random number that being unique

Question

How I can generate 5000 records in 2 columns of random numbers between 1 and 100 that being unique.

For example:

 A            B
----------------
 1            98
 1            23
 37           98
 6            56
 93           18
 .            .
 .            .
 .            .

thanks

Answer 1

Excel formulas do not perform loops until a condition has been met. Any 'loop' or array processing must have a defined number of cycles. Further, RAND and RANDBETWEEN are volatile formulas that will recalculate anytime the workbook goes through a calculation cycle.

In VBA this would look like the following.

Sub Random_2500_x_2()
    Dim rw As Long
    For rw = 1 To 2500
        Cells(rw, 1) = Int((100 - 1 + 1) * Rnd + 1)
        Cells(rw, 2) = Int((100 - 1 + 1) * Rnd + 1)
        Do Until Cells(rw, 2).Value <> Cells(rw, 1).Value
            Cells(rw, 2) = Int((100 - 1 + 1) * Rnd + 1)
        Loop
    Next rw
End Sub