Happy Friday Excellers. Today let’s look at how to generate unique usernames in Excel using the COUNTIF function, even when you have duplicates!. Welcome back for another #FormulaFriday in my #Excel 2020 series of Excel blog posts.
I have used this formula to generate usernames for a database. Sometimes there are more than one person with the same name. This great formula in Excel will helps us get over this issue.
Here is a list of usernames that has been recently updated with new users. You can see in the most recent entries we have now two people with the name Mark Browne.
This poses a problem with the naming conventions in my database. The business rules take the first 5 letters of the surname and first 5 letters of the first name. The sample can be seen in Column E.
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.
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 Create Usernames.
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.
The value in E3 is combined with the number of times the 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.
If the condition of the IF statement is FALSE then the Excel will return the value in E3. If you then drag the formula down you can populate the full data set. How cool is this?.
Testing The COUNTIF Formula.
Let’s test this by adding another Mark Browne into the data set as see the results.
You can see the three users who have the name Mark Browne now have their own unique usernames 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.