## Select 6 unique and random numbers from 1 to 49 (Lottery) in excel

### Introduction

This article describes how to randomly select 6 unique numbers from 1 to 49.

*In a 6-from-49 lotto, a player chooses six numbers from 1 to 49 (no duplicates are allowed). If all six numbers on the player's ticket match those produced in the official drawing, then the player is a jackpot winner. This is true regardless of the order in which the numbers are drawn.
Source: Wikipedia*

You learn how to:

- Create an array of 49 numbers
- Create random numbers
- Construct a criterion for unique numbers
- Create 6 unique numbers

### Create an array of 49 numbers

In cell A2 typ:

Select cell range A2:A51. Click formula bar. Press CTRL + SHIFT + ENTER

### Create random numbers

Of course, you can use this formula to create random numbers between 1 to 49:

*ENTER*.

To be able to avoid duplicates we can use **Large **function and a criterion. Simplified formula: =Large(array_numbers, Randbetween(1,49))

In cell B2:

*+ CTRL + SHIFT + ENTER*

Select cell range B2:B51. Click formula bar. Press *CTRL + SHIFT + ENTER.
*

### Construct a criterion for unique numbers

How do we remove numbers already selected, avoiding duplicates?

In our first cell C2, the formula can select any number from the number array. But in our second cell, we must remove the number in cell C2 from the number array to avoid duplicates.

ROW($1:$49) multplied by a criterion.

Adding a criterion to the simplified formula: =Large((array_numbers)*Criterion1, Randbetween(1;49))

The criterion in cell C2:

COUNTIF($C$1:**C1**;ROW($1:$49)) creates this array:

{0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}.

Cell C1 is empty and is not equal to any number in the array ROW($1:$49).

What happens in cell C3:

COUNTIF($C$1:**C2**;ROW($1:$49)) creates this array:

{0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,**1**,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}.

Cell C2 contains the number 22 and is equal to array value 22 in ROW($1:$49).

Randbetween function returns a random number between the numbers you specify. In this example the numbers are **1** and **49**.

The question is how do we calculate the number we need to specify?

(COUNTIF($C$1:C1, ROW($1:$49))=0 returns how many numbers left there are to select randomly.

{TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE}

Excel can´t sum that array so using double negations converts the array.

SUM(--(COUNTIF($B$1:B1, ROW($1:$49))=0))

SUM({ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1}) equals **49**.

In cell C2:

*+ CTRL + SHIFT + ENTER*

Copy cell C2. Paste it down to cell C51*.*

### Create 6 unique numbers

Array formula in D2:

Make sure you enter this formula as an array formula.

Copy cell D1 and paste down to cell D7.

*Press F9 to refresh list.*

**Download excel sample file for this tutorial.**

Lottery-numbers2.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**IF(**logical_test, [value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**LARGE(**array,k**)**

Returns the k-th largest row number in this data set

**RANDBETWEEN(**bottom, top**)**

Returns a random number between the numbers you specify

**ROW(**reference**)**

Returns the rownumber of a reference

### 16 Responses to “Select 6 unique and random numbers from 1 to 49 (Lottery) in excel”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Another, somewhat shorter way:

I'm generalizing and consider a sampling of k elements without replacement from a population with size n.

n=49 and k=6 is a special case.

1)Fill the columnA from A1 to An with the formula =RAND().

2)Enter the formula =RANK(A1,$A$1:$A$n,1)say into the cell B1.

3)Copy it down to the cell Bk (including).

Press F9 to refresh.

thanks a lot for shearing such a unique tricks its really easy brother. thanks thanks & thanks

Eero,

That is fantastic and yet so simple!

What are the chances Rand() returns a duplicate?

I filled a column A:A (over a million rows) with rand() values. I then calculated the number of duplicates in column(A:A) using this udf: Excel udf: Count unique distinct values in a large dataset

The calculation took about 10-15 seconds. I then pressed F9 to refresh values. I repeated the process 10 times but not once were there duplicates!

As long as you leave the numbers as they are when they are generated (that is, you do not multiply them by a wholen number and then apply the INT function in order to create a range of random whole numbers between some limits), then you can safely assume the numbers won't repeat, at least not for the way you are using them. Here is a link that mentions the repeatability of the generated random numbers...

http://support.microsoft.com/kb/828795

it all matters on luck. and i notice theres always too numbers close to each other like example (1 3 14 15 27 30) look at how 14 and 15 are in there but sometimes there not always like that. good luck fellow friends iv only won 2 dollars in my like with lotto lolz XD.

So for a project I needed to do random generated numbers, did this one in excel and I get repeats every 1 out of 8. Any ideas of keep it a set between 1-60 having 5 numbers generate randomly as possible and not have a repeat or at least such a high ratio.

Thank you! thank you! thank you!

I am a high school Math teacher and I use this routinte daily to create random calls or to distribute students randomly in groups. You've helped in the world of education.

Thanks !!!

Rene Pulupa

Poolesville HS

Poolesville, Maryland

Rene Pulupa (HS Teacher),

Thank you for commenting!

I have the following issue.

I have a list in Column A (E.g. Names) and i would like to pick 3 members (never the same) of that list in the following Cells: B2 - C2- D2

Then in the bellow row (cells: B3 - C3 - D3) i would like again to pick 3 unique members of list with names but EXCLUDING the values that i have now in B2 - C2 - D2

Any suggestions?

Prize calculation on the basis of date. Did u think about that. How a 6 digit number generated? Anybody knows, please give the formula. Urgent...

Hi There ,

Thank you for the charts but i was confused how to calculate according to my requirement.

if you could assist to get next two forecasting based on below history results, would be great.

05.01.2016 0163-68951

05.02.2016 0164-12774

05.03.2016 0165-17956

05.04.2016 0166-45887

05.05.2016 0167-26923

05.06.2016 0168-21046

05.07.2016 0169-11080

thank you.

This is simply put and very well explained. However, it generates duplicate numbers in column D. Below is the sample output I got when refreshed less than 10 times:

23

20

9

20

19

9

Umesh,

you are right. I have changed the formula in cell D2:

=IF(ROW(1:1)<7, LARGE((COUNTIF($D$1:D1, ROW($1:$49))=0)*ROW($1:$49), RANDBETWEEN(1, SUM(--(COUNTIF($D$1:D1, ROW($1:$49))=0)))), "")

I have also uploaded a new file.

Thanks for telling me.

Hi Oscar,

Your formula works great, however, I was wondering if there is capability to add another countif criteria so that it produces a random unique number different from the one above AS WELL AS the one to the left. I have 7 groups, 7 tables, and 7 rotations for which each group will move to a different table. With your formula, I am able to successfully assign each group a random order of the 7 tables to rotate too with no repeats (however I am unable to guarantee that for each rotation only one group is at each table), OR I am able to produce a schedule that has max one group per table per rotation, but then I am not able to have each group have no repeats for the tables they are rotating too. Is there a way to tweak the formula to satisfy both requirements where each group has a random 1-7 table rotation, AND for each rotation there is only one group per table? Thanks!! Appreciate the help.

Kristina,

I wrote a post regarding your question, read it here:

http://www.get-digital-help.com/2017/02/27/rotating-unique-groups-with-no-repeat/

[…] Kristina asks: […]