Finding similar names in a list of employees

[bugsly]

Hi all,

I have a list of 7000 employees and am trying to find any names that are similar to each other. I tried leveraging VLOOKUP however I can only find an exact match using this method. I also tried downloading the Fuzzy Look Up add in however I get an error about not have access to a registry when trying to use it. Does anyone know of a formula to accomplish this? I was thinking of using wildcards but am not good with figuring out excel formulas . Any help would be greatly appreciated

Thanks everyone!

 

[Drrellik]

when you say similar are you looking for Jon, John, Johnny....?? and are you looking at first names, last names or both? I would suggest simply sorting your data based on the column you want to compare. If you need to sort them back add a column and number it and use that column to resort back to the original. ~DR

 

[bugsly]

My apologies for not being clearer! I'm looking to search against the entire name, first, middle, and last as we tend to have a a lot of very similar names. (For example: aaron jones and Aron jones)

when you say similar are you looking for Jon, John, Johnny....?? and are you looking at first names, last names or both? I would suggest simply sorting your data based on the column you want to compare. If you need to sort them back add a column and number it and use that column to resort back to the original. ~DR

 

[Drrellik]

If you could post some sample data it would help other understand what you are dealing with. Again would simply sorting the data based on the name not work for you?

 

[bugsly]

If you could post some sample data it would help other understand what you are dealing with. Again would simply sorting the data based on the name not work for you?

Sure, no problem!

Here is a list of random names (very small sample, actual list has ~8000 names)

They are already sorted alphabetically. I'm looking for some sort of way to flag any names that are similar or partially matched. It is easy enough to flag duplicates but I am interested in flagging names such as those highlighted below.

[TABLE="width: 183"]
<tbody>[TR]
[TD]Gregory J. Mason[/TD]
[/TR]
[TR]
[TD]Gregory J. Meyer[/TD]
[/TR]
[TR]
[TD]Gregory J. Rhoad[/TD]
[/TR]
[TR]
[TD]Gregory J. Strodt[/TD]
[/TR]
[TR]
[TD]Gregory J. Zicarelli[/TD]
[/TR]
[TR]
[TD]Gregory K. Albert[/TD]
[/TR]
[TR]
[TD]Gregory M. Faber[/TD]
[/TR]
[TR]
[TD]Gregory M. Flucl[/TD]
[/TR]
[TR]
[TD]Gregory M. Lang[/TD]
[/TR]
[TR]
[TD]Gregory N. Lars[/TD]
[/TR]
[TR]
[TD]Gregory N. Meyers[/TD]
[/TR]
[TR]
[TD]Gregory P. Eng[/TD]
[/TR]
[TR]
[TD]Gregory P. Fran[/TD]
[/TR]
[TR]
[TD]Gregory R. Lu[/TD]
[/TR]
[TR]
[TD]Gregory R. Weis[/TD]
[/TR]
[TR]
[TD]Gregory S. Ang[/TD]
[/TR]
[TR]
[TD]Gregory S. Car[/TD]
[/TR]
[TR]
[TD]Gregory S. McKill
[TABLE="width: 183"]
<tbody>[TR]
[TD]Elizabeth H. Mueller[/TD]
[/TR]
[TR]
[TD]Elizabeth J. Hee[/TD]
[/TR]
[TR]
[TD]Elizabeth J. Morr[/TD]
[/TR]
[TR]
[TD]Elizabeth L. Szym[/TD]
[/TR]
[TR]
[TD]Elizabeth L. Wyns[/TD]
[/TR]
[TR]
[TD]Elizabeth M. Garre[/TD]
[/TR]
[TR]
[TD]Elizabeth M. Gilh[/TD]
[/TR]
[TR]
[TD]Elizabeth M. Karst[/TD]
[/TR]
[TR]
[TD]Elizabeth M. Mueller[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

[TABLE="width: 183"]
<tbody>[TR]
[TD]Thomas B. Ward[/TD]
[/TR]
[TR]
[TD]Thomas C. Bes[/TD]
[/TR]
[TR]
[TD]Thomas C. Hill[/TD]
[/TR]
[TR]
[TD]Thomas C. Mies[/TD]
[/TR]
[TR]
[TD]Thomas C. Ru[/TD]
[/TR]
[TR]
[TD]Thomas D. Zedri[/TD]
[/TR]
[TR]
[TD]Thomas E. La[/TD]
[/TR]
[TR]
[TD]Thomas E. Rosenwink[/TD]
[/TR]
[TR]
[TD]Thomas F. Kers[/TD]
[/TR]
[TR]
[TD]Thomas F. Peter[/TD]
[/TR]
[TR]
[TD]Thomas G. Shoa[/TD]
[/TR]
[TR]
[TD]Thomas G. Walz[/TD]
[/TR]
[TR]
[TD]Thomas I. Culne V[/TD]
[/TR]
[TR]
[TD]Thomas J. Busman[/TD]
[/TR]
[TR]
[TD]Thomas J. Davon[/TD]
[/TR]
[TR]
[TD]Thomas J. Kras[/TD]
[/TR]
[TR]
[TD]Thomas J. Nod[/TD]
[/TR]
[TR]
[TD]Thomas J. Paqun[/TD]
[/TR]
[TR]
[TD]Thomas J. Pluk[/TD]
[/TR]
[TR]
[TD]Thomas J. Root [/TD]
[/TR]
[TR]
[TD]Thomas J. Smot[/TD]
[/TR]
[TR]
[TD]Thomas J. Stam[/TD]
[/TR]
[TR]
[TD]Thomas J. Tkc[/TD]
[/TR]
[TR]
[TD]Thomas J. Tr[/TD]
[/TR]
[TR]
[TD]Thomas J. Yos[/TD]
[/TR]
[TR]
[TD]Thomas K. Marma[/TD]
[/TR]
[TR]
[TD]Thomas L. Willi[/TD]
[/TR]
[TR]
[TD]Thomas M. Gord[/TD]
[/TR]
[TR]
[TD]Thomas M. Gro[/TD]
[/TR]
[TR]
[TD]Thomas M. Johnson-Kai[/TD]
[/TR]
[TR]
[TD]Thomas M. Kel[/TD]
[/TR]
[TR]
[TD]Thomas M. Pay[/TD]
[/TR]
[TR]
[TD]Thomas M. Rags[/TD]
[/TR]
[TR]
[TD]Thomas M. Sax[/TD]
[/TR]
[TR]
[TD]Thomas N. Churc[/TD]
[/TR]
[TR]
[TD]Thomas N. Greenf[/TD]
[/TR]
[TR]
[TD]Thomas P. Hic[/TD]
[/TR]
[TR]
[TD]Thomas P. Janka[/TD]
[/TR]
[TR]
[TD]Thomas P. Kenea[/TD]
[/TR]
[TR]
[TD]Thomas P. McK[/TD]
[/TR]
[TR]
[TD]Thomas P. Smi[/TD]
[/TR]
[TR]
[TD]Thomas Patrick D. Mar[/TD]
[/TR]
[TR]
[TD]Thomas R. Bro[/TD]
[/TR]
[TR]
[TD]Thomas R. Ger[/TD]
[/TR]
[TR]
[TD]Thomas R. Hendric[/TD]
[/TR]
[TR]
[TD]Thomas R. Ho[/TD]
[/TR]
[TR]
[TD]Thomas R. Ho[/TD]
[/TR]
[TR]
[TD]Thomas R. Ku[/TD]
[/TR]
[TR]
[TD]Thomas R. Pau[/TD]
[/TR]
[TR]
[TD]Thomas S. Cur[/TD]
[/TR]
[TR]
[TD]Thomas S. Fans[/TD]
[/TR]
[TR]
[TD]Thomas S. Muhlha[/TD]
[/TR]
[TR]
[TD]Thomas W. Brem[/TD]
[/TR]
[TR]
[TD]Thomas W. Parki[/TD]
[/TR]
[TR]
[TD]Tianxiang Xiong[/TD]
[/TR]
[TR]
[TD]Tianyu Du[/TD]
[/TR]
[TR]
[TD]Tiffany A. Pendl[/TD]
[/TR]
[TR]
[TD]Tiffany C. Bloomfie[/TD]
[/TR]
[TR]
[TD]Tiffany F. Sand[/TD]
[/TR]
[TR]
[TD]Tiffany H. Zh[/TD]
[/TR]
[TR]
[TD]Tiffany K. Ngu[/TD]
[/TR]
[TR]
[TD]Tiffany Ki[/TD]
[/TR]
[TR]
[TD]Tiffany N. Roge[/TD]
[/TR]
[TR]
[TD]Tiffany N. Terd[/TD]
[/TR]
[TR]
[TD]Tiffany T. Ch[/TD]
[/TR]
[TR]
[TD]Tim A. Scha[/TD]
[/TR]
[TR]
[TD]Timothy A. Aub[/TD]
[/TR]
[TR]
[TD]Timothy A. Bowl[/TD]
[/TR]
[TR]
[TD]Timothy A. Ert[/TD]
[/TR]
[TR]
[TD]Timothy A. Pars[/TD]
[/TR]
[TR]
[TD]Timothy B. Neu[/TD]
[/TR]
[TR]
[TD]Timothy B. Willi[/TD]
[/TR]
[TR]
[TD]Timothy C. Bah[/TD]
[/TR]
[TR]
[TD]Timothy C. Ell[/TD]
[/TR]
[TR]
[TD]Timothy C. Fehli[/TD]
[/TR]
[TR]
[TD]Timothy C. Leu[/TD]
[/TR]
[TR]
[TD]Timothy C. Morr[/TD]
[/TR]
[TR]
[TD]Timothy C. Pauko[/TD]
[/TR]
[TR]
[TD]Timothy D. Bart[/TD]
[/TR]
[TR]
[TD]Timothy D. Bau[/TD]
[/TR]
[TR]
[TD]Timothy D. Ber[/TD]
[/TR]
[TR]
[TD]Timothy D. Bjer[/TD]
[/TR]
[TR]
[TD]Timothy D. Lauers[/TD]
[/TR]
[TR]
[TD]Timothy D. Mei[/TD]
[/TR]
[TR]
[TD]Timothy D. Pier[/TD]
[/TR]
[TR]
[TD]Timothy D. Treff[/TD]
[/TR]
[TR]
[TD]Timothy E. Stolld[/TD]
[/TR]
[TR]
[TD]Timothy E. Szi[/TD]
[/TR]
[TR]
[TD]Timothy F. Giese[/TD]
[/TR]
[TR]
[TD]Timothy G. Clu[/TD]
[/TR]
[TR]
[TD]Timothy G. Poll[/TD]
[/TR]
[TR]
[TD]Timothy J. Barr[/TD]
[/TR]
[TR]
[TD]Timothy J. Bouw[/TD]
[/TR]
[TR]
[TD]Timothy J. Brug[/TD]
[/TR]
[TR]
[TD]Timothy J. Dupp[/TD]
[/TR]
[TR]
[TD]Timothy J. Holl[/TD]
[/TR]
[TR]
[TD]Timothy J. Kess[/TD]
[/TR]
[TR]
[TD]Timothy J. Kniht[/TD]
[/TR]
[TR]
[TD]Timothy J. Kotov[/TD]
[/TR]
[TR]
[TD]Timothy J. Muel[/TD]
[/TR]
[TR]
[TD]Timothy J. Parso[/TD]
[/TR]
[TR]
[TD]Timothy J. Potm[/TD]
[/TR]
[TR]
[TD]Timothy J. Stra[/TD]
[/TR]
[TR]
[TD]Timothy J. Tur[/TD]
[/TR]
[TR]
[TD]Timothy J. Wo[/TD]
[/TR]
[TR]
[TD]Timothy K. Lo[/TD]
[/TR]
[TR]
[TD]Timothy Kim[/TD]
[/TR]
[TR]
[TD]Timothy L. Feyerei[/TD]
[/TR]
[TR]
[TD]Timothy L. Schuma[/TD]
[/TR]
[TR]
[TD]Timothy Lop[/TD]
[/TR]
[TR]
[TD]Timothy M. Avi[/TD]
[/TR]
[TR]
[TD]Timothy M. Lesl[/TD]
[/TR]
[TR]
[TD]Timothy M. Mil[/TD]
[/TR]
[TR]
[TD]Timothy P. Balca[/TD]
[/TR]
[TR]
[TD]Timothy P. Ke[/TD]
[/TR]
[TR]
[TD]Timothy P. Tho[/TD]
[/TR]
[TR]
[TD]Timothy R. Dona[/TD]
[/TR]
[TR]
[TD]Timothy R. Flee[/TD]
[/TR]
[TR]
[TD]Timothy R. Mah[/TD]
[/TR]
[TR]
[TD]Timothy R. Re[/TD]
[/TR]
[TR]
[TD]Timothy S. Brag[/TD]
[/TR]
[TR]
[TD]Timothy S. Ki[/TD]
[/TR]
[TR]
[TD]Timothy T. Toe[/TD]
[/TR]
[TR]
[TD]Timothy W. Esc[/TD]
[/TR]
[TR]
[TD]Timothy W. Har[/TD]
[/TR]
[TR]
[TD]Timothy Y. Hu[/TD]
[/TR]
[TR]
[TD]Timur I. Nezhmet[/TD]
[/TR]
[TR]
[TD]Tina A. Perki[/TD]
[/TR]
[TR]
[TD]Tina M. But[/TD]
[/TR]
[TR]
[TD]Tobi J. Fish[/TD]
[/TR]
[TR]
[TD]Tobias B. Mol[/TD]
[/TR]
[TR]
[TD]Tobias P. Torge[/TD]
[/TR]
[TR]
[TD]Tobias S. Zuer[/TD]
[/TR]
[TR]
[TD]Tobin C. Laur[/TD]
[/TR]
[TR]
[TD]Toby D. Aus[/TD]
[/TR]
[TR]
[TD]Toby D. He[/TD]
[/TR]
[TR]
[TD]Tod B. Slo[/TD]
[/TR]
[TR]
[TD]Todd D. Clar[/TD]
[/TR]
[TR]
[TD]Todd J. Hoffm[/TD]
[/TR]
[TR]
[TD]Todd P. Simm[/TD]
[/TR]
[TR]
[TD]Todd R. Weg[/TD]
[/TR]
[TR]
[TD]Todd S. Dal[/TD]
[/TR]
[TR]
[TD]Todd Walo[/TD]
[/TR]
[TR]
[TD]Tom F. Ward

Any ideas?? [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Fazza]

Suggest you get a list of employees that has a unique identifier as well as the names. Such as talk to the HR / personnel department for a list of employee id numbers and employee names, or if available query the email system for id and names data, or payroll. Then populate a field next to the names you have with that unique code - and obviously where there are duplicate codes these will be your duplicate entries.

 

[bugsly]

Suggest you get a list of employees that has a unique identifier as well as the names. Such as talk to the HR / personnel department for a list of employee id numbers and employee names, or if available query the email system for id and names data, or payroll. Then populate a field next to the names you have with that unique code - and obviously where there are duplicate codes these will be your duplicate entries.

So we do actually have a unique identifier for each employee. We have several employees with either the exact same name or with very close to the same name (greg meyer vs greg meyers). I am looking for a way to come up with a list of all duplicates (can do on my own) and all "almost duplicates" so that when we book travel for these employees we can have a double check to make sure we are booking travel under the intended employees name and not one of their close matches. Hopefully that clears things up a bit!

 

[bugsly]

We already have a program where we can enter in any employee names and it will pop up with an alert. I am just trying to come up with the initial list of employees to put into the program without having to do a lot of analysis by hand.

 

[Fazza]

OK. So there must be something I don't understand.

Post #7 starts "So we do actually have a unique identifier for each employee."

Post #8 "I am just trying to come up with the initial list of employees to put into the program ..."

Just use the good list (post #7)?

 

[bugsly]

OK. So there must be something I don't understand.

Post #7 starts "So we do actually have a unique identifier for each employee."

Post #8 "I am just trying to come up with the initial list of employees to put into the program ..."

Just use the good list (post #7)?

Thank you everyone for your help! I realize this is complicated and I am probably not explaining it the best. I have a list of employees approximately 7000 people long. They are all individual people, no duplicates. There are however some people that have either the exact same name (ex: Aaron T. Jones and Aaron T. Jones) or some people that have very similar names (Greg J. Meyer and Greg J Meyers).

When we book employee's travel arrangements, in an effort to avoid booking people incorrectly (Ex: needing to book travel for Greg MEYERS, but accidentally booking travel under Greg MEYER travel profile) we have a program that allows you to enter in known names with possible duplicates into a text file so that when we book their travel a pop up comes up saying "please double check you are booking travel under the correct travel profile".

The problem is you need to enter in the travelers to the text file that have potentially duplicate names. So we already have both Greg MEYER and Greg MEYERS included in this text file. Currently our process for adding people to the list is by booking them incorrectly inadvertantly. I would book travel for Greg Meyers, realize I booked it under Greg MEYER when Greg MEYER emails me and says, "hey I shouldn't be traveling, why do I have this itinerary?". Then we realize we have 2 people with similar names and add them to the text file. I am attempting to use the excel data to find other employees with potential similar names to add to the text file so that we get an alert when we try to book travel using their profiles. This could be done by individual analysis but I am trying to figure out a way to avoid that. Hopefully that clears things up. Let me know if that doesn't help?

Thank you again everybody for your help!