-1

I am looking for a way to keep track of the differences in two similar data sets, using conditional formatting.

Example sheet

on the local sheet I would like to see the cells that have different values highlighted, on the global sheet I would like to see just the missing "unique IDs" highlighted.

Since the order of the lists can vary, I am at a loss as how to find cell differences, based on the differences in a row, with the same unique ID, so order wouldn't matter.

Any help or nod in the right direction would be much appreciated.

player0
  • 124,011
  • 12
  • 67
  • 124
DiceB
  • 1
  • 1
  • What are the conditions you want? What values have to be different? Can you explain the conditions? – Kessy Jul 02 '20 at 13:03
  • any value apart from the unique ID could potentially be different. In the local sheet, john changed "alive" to "no" and "instrument" to "sitar", so these two cells would be highlighted. George just changed "alive" to "no", so only that cell would be highlighted. – DiceB Jul 02 '20 at 13:19
  • I do apologize but i don't get it, if all rows have they values on, you will get an entire sheet with a different color, is that what you want? – Kessy Jul 08 '20 at 14:28

1 Answers1

0

try:

=NOT(REGEXMATCH(A2, TEXTJOIN("|", 1, INDIRECT("local!A2:A"))))*(A2<>"")

enter image description here

and:

=INDEX(NOT(REGEXMATCH($A2&$B2&$C2, TEXTJOIN("|", 1, INDIRECT("global!A2:A")&INDIRECT("global!B2:B")&INDIRECT("global!C2:C")))))*(A2<>"")

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • This is definitely a step in the direction I want to go, and will be my solution if there is no other way. The sample I gave is perhaps a bit limited, but the actual data I am working on would span maybe over 50 columns. Hence it would be most helpful to see only the cells with different values highlighted, as opposed to the whole row. – DiceB Jul 02 '20 at 12:05