# Unique Random Number

#### JontyHart

##### New Member
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
One way...

A2: 1
A3, copied down:

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

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.

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)

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

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.

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

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

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.

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.

Replies
10
Views
190
Replies
4
Views
140
Replies
7
Views
226
Replies
4
Views
97
Replies
4
Views
144

1,217,439
Messages
6,136,619
Members
450,022
Latest member
Joel1122331

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

### Which adblocker are you using?

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

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