Happy Friday, Excellers. Today let’s look at how to generate a unique username in Excel using the COUNTIF function, even when you have duplicates! Welcome back for another #FormulaFriday in my #Excel 2021 series of Excel blog posts.
I have used this formula to generate usernames for a database. Sometimes there is more than one person with the same name. This great formula in Excel will help us get over this issue by generating unique user names.
Here is a list of usernames that are updated with new users. For example, in the most recent entries, we have two people with the name Mark Browne. Unfortunately, two people with the same usernames pose a problem with naming conventions in the database.
The business rules take the first five letters of the surname and the first five letters of the first name. The sample is in Column E in the screen grab.
So, let’s get clever and get Excel to do the hard work. Let’s tell it to look for a duplicate entry, and add the number 1 to the second entry, 2 to the third entry and so on.
We can use the COUNTIF formula along with the IF formula. It looks like this.
=IF(COUNTIF(E$3:E3,E3)>1,E3&COUNTIF($E$3:E3,E3)-1,E3)
The formula looks for duplicates in the data range If it finds a duplicate, then it adds 1 to the username. If there are three usernames the same then 2 is added and so on.
Breakdown The Formula To Generate Unique User Names.
Step 1.
=IF(COUNTIF(E$3:E3,E3)>1,E3&COUNTIF($E$3:E3,E3)-1,E3)
The countif statement counts how many times the contents of E3 appears in the data range. The If statement wraps this with the condition testing if the result of the COUNTIF is greater than . In this example the condition is TRUE.
Step 2.
=IF(COUNTIF(E$3:E3,E3)>1,E3&COUNTIF($E$3:E3,E3)-1,E3)
The value in E3 is combined with the number of times the user name appears, minus 1. This is done as we want the first time the name appears to only be the name itself without a number added.
Step 3.
=IF(COUNTIF(E$3:E3,E3)>1,E3&COUNTIF($E$3:E3,E3)-1,E3)
If the condition of the IF statement is FALSE then the Excel will return the value in E3. By dragging the formula down you can populate the full data set. How cool is that?
Testing The COUNTIF Formula. Generate Unique User Names.
Let’s test this by adding another user with the same name into the data set as see the results.
Finally, you can see that the three users named Mark Browne now have their unique usernames created for our database.
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money-back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.
