Search code examples
excelconditional-formatting

Conditional formatting comparing tables for missing rows and/or changes

I'm being dumb here. I don't know what I'm forgetting.

I have two tables on two sheets with similar structures. I would like to have the following conditional formatting rules: -

  • By matching on the first three columns, highlight cells in those three columns in Sheet1.Table1 that do not exist in Sheet2.Table2.
  • By matching on the first three columns, highlight cells in column four in Sheet1.Table1 that has a different value to that in Sheet2.Table2. The row must be in both tables.

The tables are sorted differently so I can't do a simple cell=cell comparison. I tried using the MATCH function, i.e. MATCH(blah)=0, but that didn't seem to do anything. I will eventually like to do this in VBA, but for now I just want to get the formulas correct and then I can write the code.

Basic example data below: -

  • Following formatting rule 1 then the cells of Table1 in rows 3 & 4 within Column1:Column3 would be highlighted.
  • Following formatting rule 2 then the cells of Table1 in rows 1 & 6 within Column4 would be highlighted.

Table1

Column1 Column2 Column3 Column4 Column5
asd 1qa abc Yes blah
qwe 2ws xyz No foo
zxc 3ed def Yes blah
qwe 4rf ghi Yes foo
asd 2ws ghi Yes blah
rty 2ws abc No foo

Table2

Column1 Column2 Column3 Column4
asd 1qa abc No
qwe 2ws xyz No
asd 2ws ghi Yes
rty 2ws abc Yes

Thanks in advance.

Solution

  • For the first part of the question, you can use Countifs (or Match if you prefer) but must use Indirect for each table reference:

    =COUNTIFS(INDIRECT("Table2[Column1]"),INDIRECT("Table1[@Column1]"),INDIRECT("Table2[Column2]"),INDIRECT("Table1[@Column2]"),INDIRECT("Table2[Column3]"),INDIRECT("Table1[@Column3]"))=0

    enter image description here

    Similarly the second part formula would be

    =COUNTIFS(INDIRECT("Table2[Column1]"),INDIRECT("Table1[@Column1]"),INDIRECT("Table2[Column2]"),INDIRECT("Table1[@Column2]"),INDIRECT("Table2[Column3]"),INDIRECT("Table1[@Column3]"),INDIRECT("Table2[Column4]"),"<>"&INDIRECT("Table1[@Column4]"))>0

    enter image description here

    EDIT

    This is a bit tedious, but you can avoid the indirect by defining the structured references in the name manager (mentioned briefly here)

    e.g.

    T1C1R2 is defined as

    Table1[@Column1]

    and T2C1 is defined as

    Table2[Column1]

    so the first formula becomes

    =COUNTIFS(T2C1,T1C1R2,T2C2,T1C2R2,T2C3,T1C3R2)=0