Search code examples
excelrandomexcel-formulaexcel-2013

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

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

Solution

  • Here is a simple-minded approach using formulae. Whether it would be appropriate would depend on context.

    First in the Formulas tab set calculation options to 'Manual'.

    Put the following formula in a2:-

    =RANDBETWEEN(1,100)

    B is going to be a helper column. Put the following in B2:-

    =RANDBETWEEN(1,99)

    Column C is the second result that you want. Put the following in C2:-

    =IF(B2<A2,B2,B2+1)

    Pull the formulae down as required.

    Each time you press 'Calculate Now', you will get a fresh set of random numbers.

    enter image description here

    However if you really need unique rows (every row to be different) you'd need a different approach - could generate a set of 4-digit numbers, split them into first and last pairs of digits and filter out ones where first and second were equal.

    Generate the 4-digit number in A2:-

    =RANDBETWEEN(1,9998)

    Take the first two-digit number plus one in B2:-

    =INT(A2/100)+1

    Take the second 2-digit number plus one in C2:-

    =MOD(A2,100)+1

    Check for invalid numbers in D2:-

    =OR(ISNUMBER(MATCH(A2,A$1:A1,0)),B2=C2)

    Set up a running total of valid numbers in E2:-

    =COUNTIF(D2:D$2,FALSE)

    Here's how the second approach would look with checking for duplicate rows as well as duplicate numbers within a row. Note that you'd have to generate about 3,000 rows to get 2,500 distinct rows:-

    enter image description here