How to generate a unique random 8 character string for each row in a column?

[Dno]

Hi,

I was wondering how I could randomly generate a string of 8 characters
(letters and numbers) down a column and have each row unique.

What I'm trying to do is assign a random 8 character password to a few hundred user names in a new column beside the user name.


Thanks!

Dino

[Dav]

The chances of 2 people having the same password is remote as there are potentially up to 4,304,672,100,000,000 different combinations

perhaps I would put the following formula in a cell and copy it down for all the number of rows I need a password for (plus a few extra in case there is a duplicate)

=CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))

then copy this somewhere else as values as it changes evry time u recalculate

then filter this list using the advance filter and choosing unique values, they can then be copied to the side of the staff you wish to have a password for.

Does that help

Regards

Dav

Nb I have included upper and lower case and punctuation characters as you did not specify what are valid characters

[Dno]

Hi Dav,

Thanks thats perfect =)

Have a great night.

[Kevin(Dublin)]

Hi Dav,

This is exactly what I was looking for, well almost!

I just want letters & numbers, alphanumeric, no symbols (* ; ' etc.)

I know the *90+33 has something to do with it but I'm not sure what to do, can you help.

Many Thanks

Kevin

The chances of 2 people having the same password is remote as there are potentially up to 4,304,672,100,000,000 different combinations

perhaps I would put the following formula in a cell and copy it down for all the number of rows I need a password for (plus a few extra in case there is a duplicate)

=CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))&CHAR(TRUNC(RAND()*90+33))

then copy this somewhere else as values as it changes evry time u recalculate

then filter this list using the advance filter and choosing unique values, they can then be copied to the side of the staff you wish to have a password for.

Does that help

Regards

Dav

Nb I have included upper and lower case and punctuation characters as you did not specify what are valid characters

[Dno]

Actually I'd like to know how to do that as well. Same thing but without Symbols. Does anyone know how to do this as well by chance?

Thanks!

Dino

[vane0326]

List your numbers and letters in column A and then input this formula in cell B2 and copy down.

=INDEX($A$1:$A$352,RANDBETWEEN(1,35))

I Left a sample workbook below.

Hope it helps!

[Dno]

Thanks great idea =)

I looked at your workbook and tried it myself and get this showing in all the fields: #NAME?

[oldchippy]

Thanks great idea =)

I looked at your workbook and tried it myself and get this showing in all the fields: #NAME?

To use the RANDBETWEEN function you will need to add the Analysis Tool Pak

http://office.microsoft.com/en-gb/ex...277241033.aspx