MrExcel.com - Your One Stop for Excel Tips & Solutions

Generate Random Without Repeats

May 17, 2022 - by

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.

Using =RANDBETWEEN(1,56) in five cells. Press F9 enough times and you might get a repeat. The screen shows 42, 49, 39, 42, and 10.
Figure 470. 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).

The formula uses MATCH, LARGE, and COLUMN to generate random numbers without repeats.
Figure 471. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Herbert Goetsch on Unsplash