How to Make Random Numbers Not Twin / Repeating in Excel

How to make random numbers or random numbers that are not twins and do not repeat in Microsoft Excel so that they produce unique random numbers.Advertisements

How to Make Random Numbers Not Twins

Some time ago there was a question to our contact about how to create unique and non-twin or repeating random numbers in excel.

As we discussed earlier to generate random numbers in Microsoft Excel, we can use the RAND or RANDBETWEEN functions.

In the excel formula, and . function will generate a decimal random number between 0 and 1, whereas Randbetween will generate round random numbers within a certain range.

If we want random numbers from 1-10 then we can use the Rand between function with the following formula:


If you make ten numbers with this formula, the result will be something like this:

Generating Random Numbers With Randbetween

If you pay attention, it turns out that by using the Rand between formula it is possible that the random numbers generated will be double or have duplicates, so that there will be several numbers that are repeated.

Then what is the solution to generating random numbers without these twins?

How to Make Random Numbers Without Duplicates

In this example, for example, we will generate random numbers from 1 to 10 that have no duplicates or twins.

To create non-repeating random numbers in Excel, the steps required are as follows.

  1. Make 10 random numbers with the RAND function, if you only need one time, convert the random numbers generated by the RAND function into numbers by copying and pasting the as the value in place. This is so that the generated random numbers do not continue to change every time Excel recalculates.
  2. In the next column, use the ranking formula with the excel RANK function to create a ranking value from the random numbers generated earlier.
  3. Finished. Now of course you have succeeded in generating random numbers 1-10 which are unique and do not repeat.
Random Numbers Without Twins in Excel

Note that the RAND function will not necessarily return a completely unique decimal number, although there is still a small chance that the RAND function will return a double random number. For that, if the Random number that you will generate is quite a lot, make sure the results are truly unique.

How do detect and eliminate duplicate data?

By utilizing the Rank function above, the smallest random number value generated is always 1, then what if we want a random number value in a certain range?

Random Number Formula Does not repeat in a certain range

For random numbers in a certain range, for example, we want random numbers that do not repeat between 20-30 using the method above, then the method is:

  1. Make a random number with the RAND function as much as the difference in the desired range, in this example, the difference is (30-20 = 10)
  2. Next, use the RANK function and increase the result by the minimum value of the desired number range. In this example, the minimum value in question is 20.
  3. And the result is…
Random Numbers Without Twins in Excel

Well, now you can of course see that the random number or random number generated by the ranking formula above is no longer a random number that has a twin, so the random value generated is unique and does not repeat itself as generated by the Randbetween function.

That’s all for our discussion this time about how to make random numbers that are not twins in excel. see you in the next tutorial. If you still have questions, please take advantage of the comments column that we have provided below.

The discussion of excel formulas related to random numbers in Microsoft Office Excel that has been discussed in this Excel class tutorial blog include: Advertisements Advertisements Advertisements

Create by iPad guides in category of Excel Class