![excel formula to remove duplicates from a list excel formula to remove duplicates from a list](https://cdn.extendoffice.com/images/stories/doc-excel/count-remove-duplicate/doc-count-remove-duolicate-1.png)
An order is omitted because we want to rank in descending order. The number parameter of the RANK.EQ function is the cell B3, while the parameter ref is the range $B$3:$B$23. The parameter range in the COUNTIF function is the cell range $B$3:B3 while the criteria is the cell B3. =9 + RANK.EQ(B3, $B$3:$B$23) + COUNTIF($B$3:B3, B3) - 1Īt the beginning of the formula, we must add 9 to the result, as the range begins from 10. Now we can use the combination of RANK.EQ and COUNTIF functions to create a list without duplicates. Using the RANDBETWEEN formula to get random numbers 10-30 list Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.įigure 2.Insert the formula: =RANDBETWEEN(10,30).To apply the RANDBETWEEN formula, we need to follow these steps:
![excel formula to remove duplicates from a list excel formula to remove duplicates from a list](https://www.lifewire.com/thmb/cBpGRummcwAyfqXUPgAfySFoANU=/1366x768/filters:no_upscale():max_bytes(150000):strip_icc()/20_how-to-remove-duplicates-in-excel-4691045-da29f89b3f414bffbac18d24a4a9f415.jpg)
The bottom parameter of the function is 10, while the top parameter is 30. As the function can generate duplicate numbers, in column C, we will generate a new list of numbers without duplicates. In column B, we will first generate random numbers from 10 and 30 using the RANDBETWEEN function. Generate Random Number List With No Duplicates in Excel criteria – the criteria for counting the cells in the range.range – the range from which we want to count the non-blank cells.The parameters of the COUNTIF function are: The generic formula for the COUNTIF function is: – a type of ranking data in the ascending or the descending order (0 or omitted value).ref – the array where we want to rank the number.number – a number for which we want to find the rank in the array.The parameters of the RANK.EQ function are: The generic formula for the RANK.EQ function is: top – a value to which we want to get a random value.bottom – a value from which we want to get a random value.The parameters of the RANDBETWEEN function are: The final result of the formula Syntax of the RANDBETWEEN Formula
EXCEL FORMULA TO REMOVE DUPLICATES FROM A LIST HOW TO
This step by step tutorial will assist all levels of Excel users to learn how to use the RANDBETWEEN function with no duplicates.įigure 1. How to Use the RANDBETWEEN Function with No DuplicatesĮxcel allows us to create to generate random numbers list without duplicates using the RANDBETWEEN, RANK.EQ and COUNTIF functions.