Comparing two ranges to conditionally format two other parallel ranges
Hey u bunch of geniuses,
I've been trying to solve this for a while, but I can't really find an input for google sheets conditional formatting that i can copy for the whole sheet.
I have two sets of datas corresponding to each scenario that I want to compare.
I want the data on the first columns of ranges to be formatted under the condition that two ranges to their respective rights differ in any way.
In the exemple below, on scene 3 there are 2 elements different, sailors and camera operators. And I would like for the area around the scene name to be red, as a warning. Here is what I would expect it to be:
Here is a reference sheet, with a formula attempt, and an unlocked copied sheet:
https://docs.google.com/spreadsheets/d/1FCNPdtkjkV2vkGzOiObUse1xKt3kKad4SgUjxKWVvcI/edit?usp=sharing
any chance anyone can help?
I've tried variations of these lines,
(the region set to format being a2:c4 , aa2:ac4 , a6:c8 , aa6:ac8 , etc....)
but they did not work:
=
IFERROR(JOIN( "" ;""; FILTER(flatten(arrayformula($d$2:$f$4)); NOT(flatten(arrayformula($d$2:$f$4)) = "") )))
<>
IFERROR(JOIN( "" ;""; FILTER(flatten(arrayformula($AD$2:$AR$4)); NOT(flatten(arrayformula($AD$2:$AR$4)) = "") )))
The above one works for only a single range(scenario). If the conditional format range input was only [a2:c4 , aa2:ac4] it would work only for highliting scenario1.0 [a2:c4), but if copied to the next scenario range would still reference the same range first range.
=
IFERROR(JOIN( "" ;""; FILTER(flatten((d2:f4)); NOT(flatten((d2:f4)) = "") )))
<>
IFERROR(JOIN( "" ;""; FILTER(flatten((ad2:af4)); NOT(flatten((ad2:af4)) = "") )))
this one doesn't work at all. 🙃
You could try a custom formula comparing the two ranges using Offset:
=ArrayFormula(let(startrow,13,diff,row()-startrow,vOffset,diff-mod(diff,7),width,12,height,6,sum(n(offset($H$13,vOffset,0,height,width)<>offset($AG$13,vOffset,0,height,width)))*(mod(diff,7)<6)))
applied to E13:G74:
