0

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!

3
  • Please provide an example; makes our task much easier. Oct 29, 2014 at 19:19
  • Welcome to Super User. It would be very helpful if you could edit your question to include more detail what you are trying to do, an example of your data and what you have already tried.
    – CharlieRB
    Oct 29, 2014 at 19:20
  • Sure -- thanks so much! Let me know if you need any more info! Oct 30, 2014 at 16:35

1 Answer 1

1

There may be neater solutions, but you shouldn't need to concatenate first and last names to make a "FullName" column.

Preview of our output

(the white cells on the right show what output our formula will produce to drive the conditional formatting):

enter image description here

Here's our formula

=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
   -COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))

Remember to adjust to suit the number of rows you have in your dataset, and the position of your first and lastname columns if they're not in A & B.

I've also assumed your first non-name data starts in cell C2 - ie this is the top-left-most bit of data you want to check for mismatches. Edit the "C"s in this formula if you need to.

Applying the Conditional Formatting

Copy the formula above (having made any changes you need to), then click on the top-left-most cell you want to check for mismatches (I'll refer to "C2" from here). Now, select the Conditional Formatting button the ribbon, and choose New Rule.

enter image description here

Select Use a formula to determine which cells to format, then paste your formula into the textbox (make sure there are no spaces - if you've copied from above you will need to strip a few out). Now you can click Format to set how you want to highlight mismatches. I've gone for a (garish!) red fill. Click OK until you get back to the spreadsheet.

enter image description here

If you have no mismatch in cell C2 then it might seem like nothing happened, but that's because we still need to apply the rule to your entire dataset. Keeping C2 still selected, click Manage Rules from the Conditional Formatting ribbon menu.

enter image description here

Now you can select the entire range you want to check for mismatches. In the Applies to box click and drag to select everything you want to compare (or if you've got lots of rows, for speed just type in the cell reference =$C$2:$Z$999)

enter image description here

Click OK and that's it!

How it works

This formula uses COUNTIFS() to count how many rows there are for that person:

=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)

Then we work out how many rows have that person's name AND the value in the column you're checking. If all the rows are identical, then this number should exactly match the first COUNTIFS().

COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))

If we subtract the latter from the former, and all rows match then the formula outputs 0 and doesn't do any conditional formatting. However if anything is different, then the output will be 1 or higher, triggering the conditional formatting.

Note

I've had to wrap the final COUNTIFS() criteria in an IF() statement to deal with blanks - COUNTIFS doesn't like blanks very much (it seems unsure whether to count them as 0 or "". Weird).


File Download

This example document is also available to download.

3
  • Thank you so much! This is great. It has created all of the columns, and I've apply the formula to all of them which refers back to the original cells and generates the numbers if there's any data conflict, however, I'm not sure how I would apply the conditional formatting as you show and mentioned -- can you explain to me how I would do that? Oct 30, 2014 at 20:35
  • @AliceGreene there you go - edited :)
    – Andi Mohr
    Oct 31, 2014 at 13:05
  • Nice answer! Well documented and explained. Good job.
    – CharlieRB
    Oct 31, 2014 at 13:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .