Random list no repeats, manual and formulas solutions

by Excel and Adam | Nov 2, 2022 | Excel

Today, we are going to talk about choos­ing a ran­dom ele­ment from a list with­out repeats. We have eight ele­ments in our list. I put there the same num­ber two times, which gives me the oppor­tu­ni­ty to choose the same num­ber two times. If there are three the same num­bers, I could choose the num­ber up to three times. How­ev­er, the choos­ing is ran­dom, so I don’t real­ly know how many times I will choose it. 

If we want to choose an ele­ment from the list at ran­dom with­out repeats, we can use a man­u­al solu­tion. We need to write the RAND func­tion which returns num­bers from 0 to 1 with fif­teen dig­it pre­ci­sion. It means that it’s almost impos­si­ble to choose the same num­ber twice (Fig. 2). 

=RAND()

After drag­ging for­mu­la down, we have 8 ran­dom num­bers. We have to remem­ber that the RAND func­tion is a volatile func­tion. It means that it is recal­cu­lat­ed every time our work­sheet changes (Fig. 3).

Now, that we have our helper col­umn, we can do the sort­ing. We go to Data tab and we choose the A to Z or Z to A command.

If we want to have, e.g. five ele­ments, we have to choose five ele­ments from the top. It does­n’t mat­ter whether we sort from A to Z or Z to A. We just choose the first five ele­ments. We can see that the num­bers are recal­cu­lat­ed every time we sort our data (Fig. 5)

Let’s get back to our orig­i­nal list and try the sec­ond solu­tion. It is from Lega­cy Excel and it uses for­mu­las. Let’s start with a way to sort our num­bers. We can use, e.g. the LARGE func­tion, which returns the biggest num­ber from the list, then the sec­ond biggest, then the third biggest, and so on. We have to press the F4 key to lock our list. Then, we need a way to choose the first, the sec­ond, and so on num­bers. So, we want to change our key argu­ment using a prop­er func­tion. One of the sim­plest solu­tion I know is using the ROW func­tion with ref­er­ence to cell A1, which is free (with­out $ signs). When we copy our for­mu­la down, our A1 ref­er­ence will also go down, and the ROW func­tion will return the first row, the sec­ond, the third, and so on (fig. 6)

=LARGE($B$2:$B$9,ROW(A1))

After enter­ing the for­mu­la, we have our num­bers placed from the largest to the small­est. Our five ele­ments are now in a prop­er order. It means that now we can choose ele­ments. How­ev­er, we must use the MATCH and INDEX func­tion in our data ori­en­ta­tion. Let’s add the MATCH func­tion to find the posi­tion of our num­ber. We have to add the range, which is our ran­dom num­ber col­umn and press the F4 key to lock it. Then, we are adding 0 because we want to have the exact match (Fig. 7)

=MATCH(LARGE($B$2:$B$9,ROW(A1)),$B$2:$B$9,0)

After enter­ing and copy­ing down, we have posi­tions of the largest num­bers. When we look at the first posi­tion in our Cho­sen col­umn, we have 5. It means that the largest num­ber from our orig­i­nal list is locat­ed in the 5th place. 

How­ev­er, we don’t want to have posi­tion num­bers, but the ele­ments con­nect­ed to those num­bers. It means that we have to add the INDEX func­tion to our MATCH func­tion. This time, we want to choose ele­ments from our list, so we are adding the range and lock it by press­ing the F4 key. It turns out that our MATCH func­tion is the row num­ber. We have to close the for­mu­la (Fig. 9)

=INDEX($A$2:$A$9,MATCH(LARGE($B$2:$B$9,ROW(A1)),$B$2:$B$9,0))

After enter­ing and copy­ing down, we have ele­ments select­ed at ran­dom with­out repeats. When I press the F9 key, I am forc­ing it to recal­cu­late our results. Some­times, we see our num­ber (123) twice because it is twice on our orig­i­nal list (Fig. 10)

It’s high time we tried the final solu­tion in Dynam­ic Array Excel. This solu­tion is sim­pler that the one in Lega­cy Excel because we just need two func­tions. In DA Excel, we have the RANDARRAY func­tion which can cre­ate a ran­dom array. We just need to copy the same col­umn into our for­mu­la, which means that we need an array with eight rows and one col­umn. That’s why we are writ­ing just 8 (Fig. 11).

=RANDARRAY(8)

After putting our for­mu­la into Excel, we have eight ran­dom num­bers. In DA Excel, we can sort by this col­umn. Thus, we have to add the SORTBY func­tion to our for­mu­la. The array which we want to sort is our list. This time, we don’t have to lock it because it is a DA for­mu­la, and our for­mu­la is the result (Fig. 12). 

=SORTBY(A2:A9,RANDARRAY(8))

After putting the for­mu­la down, we have eight ele­ments from our list in a ran­dom order. Press­ing the F9 key allows us to choose ele­ments once again at ran­dom. Let’s choose five ele­ments from the top once again. We still can see the remain­ing ele­ments. If you don’t want to see them, you have some options. You can, for exam­ple, select five ele­ments from our list, then press the F2 key to go to the for­mu­la edit mode, then press the Ctrl + Shift + Enter com­bi­na­tion to cre­ate an array for­mu­la. The array for­mu­la is from Lega­cy Excel. Now, we have only five ele­ments because I’ve select­ed only five cells. Those cells are an array, which means that we can­not change or delete any sin­gle ele­ment of the array. If you want to change it, you have to change the whole array at one time (Fig. 13).

Sum­ming up, if you want to choose an ele­ment at ran­dom from a list, you can use a man­u­al solu­tion, a Lega­cy Excel solu­tion or a Dynam­ic Array solution. 

https://www.youtube.com/watch?v=myT-vQzDSeE