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
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
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.
Best Answer
Assuming your left-most "Col1" header is in cell
A1
, select cellsE2:F6
(withE2
as the Active Cell), and use this formula for Conditional Formatting:Result: