• Excel Book Excerpt

Excel Generate Random Without Repeats

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Generate Random Without Repeats

Problem: I want Excel to generate numbers for the lottery. Once a number is chosen, I don’t want that number to appear again. Using RANDBETWEEN, it is possible to get duplicates.

LE10000485.jpg

Figure 477 Eventually, RANDBETWEEN returns duplicates.

Strategy: to solve this problem, you need to sort the 56 numbers into a sequence and choose the top five numbers from the list. This will prevent any duplicates from showing up.

Say that you want to generate five numbers from 1 to 56. Follow these steps:

1. Select a range that is one column wide by 56 rows tall.

2. Type =RAND(). Press Ctrl+Enter to enter that formula in all of the cells. In my example, I used A1:A56.

From here, you want to find the largest values using =LARGE(A1:A56,1) then =LARGE(A1:A56,2), then LARGE(A1:A56,3), and so on. Once you locate the largest value, use MATCH to find that value within the list. The position in the list represents the lotto number.

3. Combining all of those formulas together, you get =MATCH(LARGE($A$1:$A$56,COLUMN(A1)),$A$1:$A$56,0). Enter this formula in C2:G2.

4. For the extra ball, use a regular old =RANDBETWEEN(1,46).

LE10000486.jpg

Figure 478 You won’t get any repeats in C2:G2.

Additional Details: For PowerBall, enter numbers in A1:A59. Change the 56 in the formula above to a 59. Change the formula in H2 to get numbers from 1 to 39.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: