Forum Discussion
How to compare Cells that have multiple names for match and mismatch
Hello Please what formula should i use to compare two cells that have more than one name. I want to compare names in two columns but the cells have both the first name, middle name and last. Is ther...
So much here, I would think, depends on the magnitude of the issue: How may rows of names are we talking about?
Anyway, if I were in your place, I'd look at some combination of:
- parsing out first, middle and last names into their own columns (using some of the text manipulation functions like FIND, MID, RIGHT and LEFT), the purpose being to compare Last with Last, First with First, etc. because you probably have not only misspellings but some who use periods after middle initials, some who don't, etc.
- comparing based not on the whole of a name but only on the first, say, four characters. And if you've separated the names into three columns each, you could do this and give the comparisons "scores" on whether all three matched, or only two of three etc.
That's just for starters. There may be ways you could use wildcard characters as well. But as I said at the start, it all depends on magnitude.
I'm sure you'll get some other suggestions; let's home some are more helpful.