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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
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)
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
{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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top