1

I have a spreadsheet containing names.

I have 5 columns of unique names, let's call the headers:

Name A
Name B
Name C
Name D
Name E

I also have 10 columns of other names (not necessarily unique), let's call the headers:

Name 1
Name 2
Name 3
etc.

What I would like to do is compare the value in Name 1 to Name 10 against the values in Name A to Name E and highlight (using conditional formatting perhaps) any values in Name 1 to Name 10 that DO NOT appear in any of the columns Name A to Name E.

If anyone has any ideas on how to do this with conditional formatting (or by any other means) I would be extremely grateful!

1
  • If you provide a link to the image someone with the ability to add it as an image can insert it. What have you tried so far with conditional formatting formulas? Perhaps an if statement comparing the number of times the current cell appears in an offset block of 10 cells using countif would meet your needs? Mar 27, 2015 at 4:26

2 Answers 2

1

I rewrote the answer completely after seeing your sample (you need to work more on how to express your problem),

First, I'm posting the request image as is, so people would understand the problem:

enter image description here

You can achieve that using two formulas:

For cells in G column (Name 1) use the following:

=AND(NOT(ISBLANK(G2)),ISERROR(MATCH(G2,$A2:$E2,0)))

For the other columns, use this:

=AND(NOT(ISBLANK(H2)),ISERROR(MATCH(H2,$A2:$E2,0)),ISERROR(MATCH(H2,$G2:G2,0)))
8
  • Thanks so much for the tips, and the quick response. I have tried using this formula but can't seem to get it to work. I'd be happy to share my mock-up of how I'd like the results to display and/or a sample of the actual spreadsheet that I'm working with if you'd like to email me. Mar 27, 2015 at 2:36
  • @ColinJones you can upload your file to dropBox or Google Drive and post the link to your file in the question, you don't have to share your original file if it contains sensitive info, just a sample of what your data looks like would really help us in helping you.
    – tbc
    Mar 27, 2015 at 9:04
  • @ColinJones I updated my answer, have look and till if something is still missing
    – tbc
    Mar 27, 2015 at 9:48
  • Hi again, one point of clarification, I am only looking to compare the values in columns G through P to the same row in column A through E, not the entire sheet. Here is a link to a mock-up screen capture with a few additional comments drive.google.com/… thanks so much for your continued help with this! Mar 27, 2015 at 14:23
  • After playing around with this I figured out how to limit the comparison to only the same row. I used the following =NOT(OR( IF(NOT(ISERROR(LOOKUP(G1,$A1))),LOOKUP(G1,$A1)=G1), IF(NOT(ISERROR(LOOKUP(G1,$B1))),LOOKUP(G1,$B1)=G1), IF(NOT(ISERROR(LOOKUP(G1,$C1))),LOOKUP(G1,$C1)=G1), IF(NOT(ISERROR(LOOKUP(G1,$D1))),LOOKUP(G1,$D1)=G1), IF(NOT(ISERROR(LOOKUP(G1,$E1))),LOOKUP(G1,$E1)=G1))) Mar 27, 2015 at 19:39
0

If you wish to avoid using a lengthy formula, Excel conditional formatting has an existing rule you can use. It may be too simplistic for what you are trying to do, but if you highlight all the data you can select conditional formatting and choose the rule "Format only unique values". Anything highlighted in the second set of data would not appear in the first set of data. It will not work though for any duplicate values in the second set of data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.