Today let’s learn how to generate unique usernames in Excel, even when you have duplicates in your data.
Formulas Used
1) IF( condition, [value_if_true], [value_if_false] ) Learn More about IF()
Parameters or Arguments
- condition
- The value that you want to test.
- value_if_true
- Optional. It is the value that is returned if condition evaluates to TRUE.
- value_if_false
- Optional. It is the value that is return if condition evaluates to FALSE.
2) COUNTIF( range, criteria ) Learn More about COUNTIF()
Parameters or Arguments
- range
- The range of cells that you want to count based on the criteria.
- criteria
- The criteria used to determine which cells to count.
I have used this formula to generate usernames for a database, as sometimes there are more than one person with the same name and 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. That will pose a problem with the naming conventions in my database.
Create dependent drop down lists containing unique distinct values in excel
The naming conventions are, the first five letters of the surname and the first five letters of the first name. In my example batch of data my surnames are stored in cell beginning D3 and my first names beginning in cell C3. The result of the generated usernames begin in cell E3.
I have however in my data a few people with the name Mark Browne which generates the username BrownMark. Ideally what we need is for Excel when it comes across a duplicate entry it adds another character to the name to make it unique in our database.
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)
Find max unique value from a range that have duplicate numbers and blanks
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.
If we break down the formula.
=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 1… and the condition is TRUE…
=IF(COUNTIF(E$3:E3,E3)>1,E3&COUNTIF($E$3:E3,E3)-1,E3)
… then the contents of E3 is combined with the number of times the name appears, minus 1 as we want the first time the name appears to only be the name itself without a number added.
Excel Tips & Tricks for a Successful and Efficient Data Analysis
=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. If you then drag the formula down you can populate the full data set.
Let’s test this by adding another Mark Browne into the data set and see the results
The three users who have the name Mark Browne now have their own unique usernames for our database.