Unique Random Number

JontyHart

New Member
Joined
Aug 23, 2006
Messages
6
In column A, I want to generate a random and unique number for each row in my worksheet. Is there a formula to do this in Excel? In MS Access, I would have achieved this through use of a Primary Key.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
One way...

A2: 1
A3, copied down:

=LOOKUP(9.99999999999999E+307,$A$2:A2)+1

+1 is added on Edit.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,026
A primary key is not a random value. In your case, just use =ROW()

In column A, I want to generate a random and unique number for each row in my worksheet. Is there a formula to do this in Excel? In MS Access, I would have achieved this through use of a Primary Key.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,026
Not like you to make a mistake. You must be having an off day. {grin}

The formula as posted simply yields 1. LOOKUP(...)+1 might be what you meant. Though, MAX($A$2:A2)+1 might be safer.

One way...

A2: 1
A3, copied down:

=LOOKUP(9.99999999999999E+307,$A$2:A2)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Not like you to make a mistake. You must be having an off day. {grin}

No one's cognition is excluded from making errors, committing omissions, etc.

The formula as posted simply yields 1. LOOKUP(...)+1 might be what you meant. Though, MAX($A$2:A2)+1 might be safer.

You seem to enjoy the occasion. It's a valid formula even in OpenOffice (Version 2.0).
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hi Aladin.

You are the most fantastic formula guru and you have helped me in the past, for which I thank you very much.

However, your formula just generates 1s for me - Excel 2003 and Windows XP.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Hi Aladin.

You are the most fantastic formula guru and you have helped me in the past, for which I thank you very much.

However, your formula just generates 1s for me - Excel 2003 and Windows XP.

It should have been:

=LOOKUP(9.99999999999999E+307,$A$2:A2)+1
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,026
{snip}

You seem to enjoy the occasion.
That was not my intent. My apology if that's the interpretation that came across.
It's a valid formula even in OpenOffice (Version 2.0).
I have no clue what OpenOffice has to do with anything. But, a '+1' term is needed to generate unique numbers as I noted in my post; and, in any case, MAX()+1 is safer.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
generate a random and unique number for each row in my worksheet. Is there a formula to do this in Excel?

Has anybody noticed the random and unique part of the OP's request?

Now I really, and I really mean really, don't want to get into any arguments with anyone but as far as I can see so far none of the formulas suggested do what the OP asked for.

All the formulas posted so far seem to just create sequential numbers down a column.

Now, I don't know if it's me but that doesn't appear to be random.
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
I think things became a little unclear when it emerged the solution to the problem in access was the using af a primary key, which is not a random number by nature (but is unique!)

Surely the logic would be to generate a random number, and use a procedure to determine if that random number has been generated previously in the list. If not, its random and unique, otherwise generate the random number again.

I could do it with VBA, but as I have seen over my time on the board, there are some amazing formula people (like Aladin) who could probably generate something to do that with their eyes shut.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,059
Messages
5,545,759
Members
410,704
Latest member
Cobber2008
Top