Sorry I saw your Excel discussion and I had to chime in. I use Excel for work and have to do almost the exact same task you're describing- comparing and normalizing user data to get usage rates. There's an easy way I use to at least look for similar duplicates using only a couple of excel formulas.
Basically you can get Excel to check for duplicates based on first and last initials and birthday. First you need to put your data in a readable state for that by adding columns for first and last initials.
Are the first and last names already in separate columns? You can skip to the star. If not, you should separate them into columns using the "text to columns" tool under data. Set it to separate them by symbol, then select Space.
Joe Blow will now be in two separate cells, Joe and Blow.
☆Then, to get the initials, add two more columns and enter =LEFT(A2, 1) where A2 is the cell with the name. This formula returns the character most to the left, and 1 is how many letters you want. For Joe, this gives you J. If you did =LEFT(A2, 2) it would give you Jo.
Now you have all the parts,
| Joe | J | Blow | B | 1960/12/06
Next we'll check for duplicates. Add another column to the far right and enter formula =COUNTIFS(column for all first name initials, cell with Joe's first initial, column for all last name initials, cell with Blow initial, all birthdays column, birthday cell)
This basically tells excel, look at all the first and last initials and the birth dates and see if any of them match Joe's: J B born dec 6 1960. Return the number of matches you find.
If the number is 1, and there's only one user with initials JB and birthday on that day. if it's two or higher then it might be a duplicate. You can filter out the 1s and check the 2+ entries to see if those are actual duplicates or something like Joe Blow and Jane Brown born on the same day.
You can then mark the dupes and not count them when doing user counts. An easy way to do this is to add a new column and put a 1 on all the accounts that are not duplicates and a 0 on the duplicates, and then just sum up that column to get a estimated user account. You can also delete the duplicates entirely if that won't fuck up your data, and then count.
Anyway i know you have IT to help, and I don't even know if this is the best way, but I just wanted to add my two cents. Good luck with your data!
ohh that's all good info, thank you!
