Excel: Generate Random Without Repeats

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


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.

  1. 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).

  2. 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.