1

I have two spreadsheets, an original and a modified, and I would like to determine if certain collections of cells in a given row in the modified do not exist in the original.

For example, if I have my original and modified data side by side in the same sheet as so

enter image description here

I would like to identify rows in the modified where Col1 and Col2 together do not exist in the original data ignoring the values in Col3 as so

enter image description here

I think I should be able to do this with a custom formatting rule based upon a formula but I am not sure how I can apply a rule like this in a row order agnostic manner.

I have previously asked a question about doing something similar but this question assumed that all rows in the original, in a sense, match up with the modified. But I have found in the modified spreadsheet rows can be switched.

Improve this question
0

1 Answer 1

Reset to default
2

Assuming your left-most "Col1" header is in cell A1, select cells E2:F6 (with E2 as the Active Cell), and use this formula for Conditional Formatting:

=COUNTIFS($A:$A,$E2,$B:$B,$F2)=0

Result:

enter image description here

Improve this answer
3
  • I assume I can follow the pattern to include or ignore more columns as I like?
    – KDecker
    Commented Nov 10, 2016 at 17:27
  • @KDecker yes, you can add columns to the COUNTIFS.
    – Scott Craner
    Commented Nov 10, 2016 at 17:30
  • I was upset with myself for a second because I thought I had applied this wrong. Then I realized that I actually corrected modified the spreadsheet as I wanted. Thanks again MJH and Scott Craner!
    – KDecker
    Commented Nov 10, 2016 at 17:34

You must log in to answer this question.

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