Need excel formula to create unique usernames.

I really need to get this done as soon as possible. Any help will be greatly appreciated. I would look in seperate threads but after surfing the net for about 3 hours I cannot find anything that will help me in this situation. I am using Microsoft Excel 2010, btw.

So far I have a column A which has the firstname and Column B which has the last name of the users and i want to make a unique user name for them. I use the formula =LOWER(CONCATENATE(LEFT(A2,1),LEFT(B2,5),)) to create a username e.g. John Smith being jsmith; however there are multiple John Smiths that I need to create usernames for. Is there a way to use an IF function to tell if the username already exists and if so to add a digit, starting with 0, on to the the username making it unique, e.g. if i have 4John Smiths the usernames would be jsmith0, jsmith1, jsmith2, and jsmith3, etc...?

If there is not way to do this using a formula can someone please point me in the direction of asking the proper people?

This issue is very urgent as i need to create usernames daily and about 1500 per year in mass. The person who used to handle user name creation is no longer here, but I feel most comfortable doing this in excel.

Thank You for all your help!

This is possible with the match function but very dangerous.

imagine that you get the formulas working and then decide to insert another user, or even to sort the users.

Sorting by any criteria, Age, ***, Firstname, Surname, Date Of Birth, Payroll number, will reorganise your users in the column.

Unfortunately all your added numbers will change. Wooooooops.

I therefore think you need to use a Macro to do this.

This Macro will use the match function, but once allocated the added number can be fixed using the copy paste value function.

There are numerous ways that the macro could operate. but possible the bestway is using a userform. So what data do you need to collect for each user?

Please consider that while I ponder on this.

Something like this?

I always pre-sort the data before Im going to create the usernames. This is more like a one time thing. I do not need to keep the file after the usernames are created.

Basically, what ill be doing is pulling a list of all users and usernames from our LDAP server and compiling them to and excel file. Afterward I will merge this list with new user data and by sorting the users that do not have usernames last copy the formula to those users. Once this process is done i basically copy the usernames, create passwords for them in a seperate program, and finally insert them through a mass import to their respective LDAP ou's.

Ultimately I would like to stay away from a macro because no one in my office except for me knows how to run macro's nor do i have the time to teach them. In case of my absence I need someone to be able to do this one the fly perferable with a formula; but if there is no other way i can settle for anything at this point.

Thanks again for all your help! Im really lost on this one.

To popipipo

This seems like it might work for my uses. Im going to test it out and get back to you.

Once again thanks for the help.

I would use something like this.

There is a lot of functionality built in.

I will try add complete this project tomorrow, but I thought you might like a preliminary view.

I have got the find button working for birthdays and payroll numbers.
when searching birthdays ( usernames and first and last names will be similar ), the userform populates the combobox

selecting a item from the combobox will populate the userform and highlight an entry. Delete will delete that entry.

entering a payroll number and pressing find will populate the userform and highlight an entry. Delete will delete that entry.

pressing clear will clear the userform

Ok

I Have finished this project to my satisfaction.

Enjoy it and feel free to adapt it as required.

Similar circumstance but have new accounts to add to existing accounts using the next number available. For example: In list A jsmith may already has jsmith1, jsmith2, jsmith3. In list B I have an addition jsmith I would like to import the next number, jsmith4. What is the formula for comparing the two lists and returning the value of last number after jsmith in list B? If not a duplicate then return jsmith0 in list b.