Conditional formatting formula to highlight changes based on text in two columns

[smide]

Hello.


In columns Z-AB (Z3:AB800) I have a list of different towns (column Z), customer names (column AA) and buying price for each customer column AB.


After a while I'm getting a new list but with similar names and towns in columns AD-AF (AD3:AF800).


First I need to compare columns for towns and customer names on both lists and if I find the identical on both THEN to highlight cell for value on new list IF that value is different from those on old list (Z-AB).


example.
* if new name OR new town appears on new list highlight that value also








[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]AA[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AC[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]AE[/TD]
[TD="align: center"]AF[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Cologne[/TD]
[TD]B. Otto[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD]Warsaw[/TD]
[TD]J. Thorsen[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Warsaw[/TD]
[TD]J. Thorsen[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD]Cologne[/TD]
[TD]B. Otto[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Brno[/TD]
[TD]M. Fasko[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD]Brno[/TD]
[TD]M. Fasko[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Cologne[/TD]
[TD]A. Canbaz[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: start"]Munchen[/TD]
[TD]R. Becker[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"].....[/TD]
[TD="align: center"].....[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"][/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]....[/TD]
[/TR]
</tbody>[/TABLE]


highlight explanation:
- row 3 on new list is the same (town and customer name) as row 4 on old list and value changed from 0 to 1, so highlight cell AF3
- row 4 on new list is the same as row 3 on old but value changed from 7 to 8, so highlight cell AF4
- row 5 on new list is the same as row 5 on old and value is also the same so do nothing with cell AF5
- row 6 on new list is with new town and/or new name so highlight cell AF6 anyway

 

[Eric W]

Select column AF. Click Conditional Formatting > New Rule > Use a formula... and enter:

=AND(AF1<>"",OR(COUNTIFS(Z:Z,AD1,AA:AA,AE1)=0,SUMIFS(AB:AB,Z:Z,AD1,AA:AA,AE1)<>AF1))

Click Format... and choose a fill color.