Excel Random Number Generation and Deduplication Guide

Quick link:

• How to generate random numbers
• How to fix the generated random number
• How to remove duplicate random numbers

Generating a list of random numbers in Excel is very convenient for randomized lists, statistical sampling, and many other uses. However, Excel's random number functions are volatile, which means they are constantly changing. In this article, I will show you how to generate and fix random numbers and remove duplicates.

How to generate random numbers

Excel has three functions that can generate random numbers:

After entering the formula and pressing Enter, you can use Excel's fill handle to create more random numbers using the same conditions. However, be careful when using RANDARRAY's fill handle - if you drag the fill handle to the cell containing the results of the initial RANDARRAY formula, you will see a #SPILL! error and the random number array will be interrupted.

How to fix the generated random number

All three random number functions listed above are volatile functions, meaning they are regenerated every time they change the worksheet or reopen the workbook.

So to fix the generated random numbers (I used the RAND function in the example below), select the cell containing these numbers, right-click the selected cell, and click Copy. Alternatively, select the cell and press Ctrl C.

Now, place the cursor in the upper left cell of the copied range, click the Paste drop-down arrow on the Start tab of the ribbon, and then click Value (the icon containing the numbers 1 to 3). ). Or, press Ctrl Shift V.

These numbers are now fixed, just like you would manually enter them into the cells. Essentially, you create numbers using a random number function and then use Paste Special to pin them.

How to remove duplicate random numbers

Before I show you how to remove duplicate values ??from a list of random numbers, it is worth noting that of the three functions listed above, the RAND function is least likely to return any duplicates because of the list of numbers it generates Contains up to 15 decimal places.

By entering FALSE as the final parameter to return a decimal number, you can also increase the chance of avoiding duplicate numbers when using the RANDARRAY function.

However, since RANDBETWEEN only uses integers (integers only) and has upper and lower bounds, whether it is possible to return duplicates depends on the range you specified - the larger the range, the less likely the function is to return duplicate numbers.

There are several ways to create a list of random numbers without duplicates from a blank canvas, such as combining INDEX, UNIQUE, RANDARRAY, and SEQUENCE functions. However, our goal is to remove duplicates from the list of random numbers that have been created. Furthermore, combining these functions is much more complex than the simple steps I'm about to show you.

The following steps assume that you have fixed the random number (as described in the previous section). They also assume that all random numbers are in a column.

First, select all cells containing fixed random numbers. If you have a long list of numbers, selecting the entire column will be faster. Then, in the Data tab, click Delete Duplicates.

Next, make sure the details in the Delete Duplicates dialog box are correct. In my case my data is in column E and there is no title, so I can just click OK.

Excel will then tell you how many duplicates it deletes. In my example, 50 appears three times in the list and 19 appears twice, so Excel deletes two 50s and one 19, deleting a total of four duplicates. Click OK to close this message.

Now, since the data is missing four numbers, I need to generate more random numbers using the same random number function as in the original list and fix them like in the previous step. Once you are done, select the entire list of numbers again, click Delete Duplicates in the Data tab, and repeat the process until there are no random numbers anymore.

In addition to copying and pasting values ??in cells containing variable functions to fix them, you can also stop all variables by clicking Calculation Options in the Formula tab and selecting Manual Functions are calculated automatically. Then, after entering the random number function, click Calculate Now to update the random value.

The above is the detailed content of How to Generate, Fix, and Remove Duplicates from Random Numbers in Excel. For more information, please follow other related articles on the PHP Chinese website!