Checking for entries that shouldn't exist in more than 1 column

[mcongdon]

Hey everyone!

I have an Excel 2007 spreadsheet containing 2 columns of names. One with last name, one with full name. I was wondering if there is any way to check the list of full names to see if it contains any of the last names in the other column. If there is, highlight the full name that has the last name from the other column.

This is the simplified version of a very complicated process. Basically, I have a CRM system that i've exported 2 lists of names from. Leads that have been converted to contacts should not still exist. The duplicate checking that is built in is pretty good and I think it did it's job, but I need to be 110% sure. Any help would be greatly appreciated.

 

[Gavona]

Assuming Fullname consists of Firstname space Lastname then you can extract [red]Lastname[/red] and try to match it to the lastnames list.

This formula assumes Fullname is in A2 and Lastnames are in C2:C5.

=NOT(ISNA(MATCH([red]MID(A2,FIND(" ",A2)+1,99)[/red],$C$2:$C$5,0)))

To achieve the highlight use Format, Conditional Formatting, Formulas is.....

If fullnames are in cells A2 to A30 then I would select this range. Format, Conditional Formatting... and hold Ctrl down as you click OK. This applies the conditional format to the entire range. Or you can simply use Copy, PasteSpecial, Formats

Gavin