Similarly, the functions = RANDOM and = RANDOM.ENTRE allow us to generate a series of numerical data and also the generalization of the letters in a casual way, but also without having to repeat them. But it is possible that the situation is not limited to a single prize, but to multiple prizes, and it is necessary that an already assigned number cannot go out again.
That is, if we use the option = RANDOM.INTER To get all the winners, one of the numbers is likely to come up two or more times. There is a way this doesn't happen, and that is through the use of one of the functions already mentioned, supported by a new one, these are: = RANDOM and HIERARCHY.
What is the hierarchy function for?
Technically, this function is not used to generate random numbers in Excel, but can help you contribute to the task of don't repeat them if you try to do it repeatedly. This function has the textual purpose of returning the hierarchy or position of a number within a list of numbers. You should also know that you can also compare two lists and verify that no data has been repeated.
It is commonly used to sort numerical data according to whether they are major or minor, for example, and sort them in ascending or descending order as appropriate.
For this case, = HIERARCHY has a utility that helps dissolve duplicate numbers, which can also be deleted, which works with the = COUNTIF function.
It usually works just in case repeat two values, this command helps to group them in similar positions, but giving priority to the number with best secondary values.
In a way, this function can help, if you have the ingenuity, to override a value during the generation of random numbers in Excel.
This happens because one of the function's criteria allows a number from thrown again, since it = COUNTIF, said data is not repeated.
Steps to generate random numbers in Excel
In case there is a lottery number in which you have to match 5 numbers from 1 to 29. And the combination of the prize is a series of 5 numbers that do not repeat. In this case, should be used the function = RANDOM.BETWEEN, but there is a possibility that one or more numbers will come repeated in the process.
To avoid this, it is necessary to develop a two-step method so that the fortuity data without repetition is effective. The point is, you need to create a quantity of 29 random numbers using the function relevant in a column.
This will contain the name RANDOM in column A1 and below from 2 to 30 the following = RANDOM() will be placed in the formula bar. That will produce some random numbers.
In column C we will do something similar, but this time we will use = RANDOM.BETWEEN() and the limit of values we want expressed in this way (1/29/XNUMX; XNUMX/XNUMX/XNUMX). Do something similar to the following formula = RANDOM.INTER (1; 29), thus producing a random number varying between 1 and 29 (producing repeated data in certain cells).
In the second step, for generate random numbers in Excel without getting repetitions, as can happen in the previous case, we will use the following formula for example in column E.
To conclude this passage, we just have to write the following function in each of the relative boxes of column E: = HIERARCHY (A2, $ A $ 2: $ A $ 30), being the one corresponding to column E2. In the case of E3 it would be like this: = HIERARCHY (A3; $ A $ 2: $ A $ 30).
So, let's put the formula = HIERARCHY (A2, $ A $ 2: $ A $ 30) in box F2, and selecting it, it will perform a random sorting of the data each time, giving a hierarchy to each of them.
