Random Passwords and usernames

[rbsmit02]

I have 500 agents trained on a new tool.
I need to create usernames and passwords for them as I am the admin of this tool.

What I need is thus:
column 1 contains first name space last name.
I want column 2 to format the name to a string in the format first initial last name (ray smith = rsmith).

In the third column I need to generate a random password between 6 and 8 charicters with at least 2 letters in the password.

Any pointers on how to do this?
this project needs to take very little of my time so I do not care what type of solution it is.... VBA or formula

 

[rbsmit02]

ok, ive requested the training department create the spreadshet thus:
a1 = last name
b1 = firstname
I then use this formula to generate the username

=LOWER(CONCATENATE(LEFT(B2,1),LEFT(A2,7)))

This works well.

Now I just need to know how to generate the password with the correct criteria.

I am going to add that into a macro that also checks for duplicate usernames and adds a number to the end to make it unique.

Anyone care to help with the password generator?

 

[Zack Barresse]

Hi,

I don't have time right now to do a random password, but this will give you your usernames ...



<font face=Tahoma New>
<SPAN style="color:#00007F">Sub</SPAN> CreateUsernames()
    <SPAN style="color:#00007F">Dim</SPAN> strUserName$, strFName$, cel <SPAN style="color:#00007F">As</SPAN> Range, rng <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Set</SPAN> rng = Range("B2", Range("A65536").End(xlUp).Offset(, 1))
    rng.Clear
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cel <SPAN style="color:#00007F">In</SPAN> rng
        strFName = cel.Offset(, -1).Value
        strUserName = LCase(Left$(strFName, 1) & Right$(strFName, Len(strFName) - _
            WorksheetFunction.Find(" ", strFName, 1)))
        cel.Value = strUserName
    <SPAN style="color:#00007F">Next</SPAN> cel
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>



HTH

 

[sykes]

Hi

This will produce the username without separating out the columns:

=LEFT(A18)&RIGHT(A18,LEN(A18)-FIND(" ",A18))

Place into any column except col A, then drag formula up and /or down.

Random password mmmmmm

 

[Oaktree]

Here's a "rough draft" shot at the random password:

o237 houses the numbers 0-9 and the letters A-Z.

C2: =RANDBETWEEN(6,8)
D2: =IF($C2>=D$1,OFFSET($O$1,RANDBETWEEN(1,36),0),"")
L2: =D2&E2&F2&G2&H2&I2&J2&K2
M2: =IF(SUM(ISNUMBER(VALUE(LEFT(L2,1)))+ISNUMBER(VALUE(MID(L2,2,1)))+ISNUMBER(VALUE(MID(L2,3,1)))+ISNUMBER(VALUE(MID(L2,4,1)))+ISNUMBER(VALUE(MID(L2,5,1)))+ISNUMBER(VALUE(MID(L2,6,1)))+ISNUMBER(VALUE(MID(L2,7,1)))+ISNUMBER(VALUE(MID(L2,8,1))))<C2-2,"yes","no")

Book13
	C	D	E	F	G	H	I	J	K	L	M	N	O
1	length	1	2	3	4	5	6	7	8	full	valid?
2	6	X	C	X	S	F	Z			XCXSFZ	yes		0
3													1
4													2
5													3
6													4
7													5
8													6
9													7
10													8
11													9
12													A
13													B
14													C
15													D
16													E
Sheet1

 

[rbsmit02]

well gee thanks,
Both work just fine.

Still searching the board for the password issue though

 

[Oaktree]

Formula in M2 above got cut off. Should be ...
+ ISNUMBER(VALUE(MID(L2,8,1)))) <C2-2,"yes", "no")

edit: Grr... Can't get the whole formula to show up. Oh well, it's in the cell.

 

[sykes]

Quick edit of my effort....


In order to go into any col and drag the formula, it needs to be put into row 18, and changed thus:

=LEFT($A18)&RIGHT($A18,LEN($A18)-FIND(" ",$A18))

 

[rbsmit02]

oaktree,
I appreciate the effor but I cannot use the randbetween...
cant install the Analysis ToolPak add-in
system lock-down and such

 

[rbsmit02]

sykes
Thanks. It works either way I put the formula.