I need to conditionally format cells with different values between the same columns, if their value for a specific column is the same.
Scenario
My spreadsheet is a contacts database, where each row is a contact record, and each column contains a type of data (last name, street 1, street 2, city, state, etc) for each contact record.
The spreadsheet is an amalgamation of the contact information kept by different offices in our company -- we've combined all of the contact information into the same source so we can agree any discrepancies and keep all of our contacts in the same place going forward.
Some contacts only have one record--such as when only one of our offices had data for that contact, so it can't disagree with data we have from any other office; other contacts have several records (i.e. there might be only one row for Fred Johnson but four rows for Jane Smith). Every contact needs to have the same exact contact information before we can upload it into a cloud database or it will create duplicates.
We've spent a long time trying agree data between records for the same person, and now we need to find out where the remaining discrepancies exist.
I've concatenated the LastName and FirstName fields so that rows with the same value in this field can be compared against each other -- the goal is to highlight cells with different data in the same column when compared against other records with the same value in the LastFirst column (if any).
How can I do this?
So far I'm aware that the Find Special function can highlight cells when compared against a designated row or column, but I'm needing the comparisons to be made against rows that have the same value for a particular column.
I have access to Excel 2010 and 2013 to perform this function.
Thank you!