Im trying to highlight partial duplicates in google sheets using conditional formatting. I need to highlight duplicates across multiple columns and rows in any matching cells. so far I'm using this...
=countif(INDIRECT({"C2:Z18"}),C2)>1
Which works to a degree, but it only highlights exact matches. I need to find a way to highlight partial matches also. The data I'm using is basically a 4 digit bus number followed by a fuel reading, like this...
1429 1384 1429/167
I need to find a way to get the conditional formatting formula to only match the first 4 characters of each cell. So that it ignores the following fuel reading. I have tried this...
=COUNTIF(INDIRECT({"C2:Z18"}),LEFT(C2,4)&"*")>1
But it malfunctions and highlights every cell in the selected range, which is listed in the conditional format rule.
Does anyone have any ideas?
Thanks to a genius over at the Google Product Support Forums named Isai Alvarado I finally have the solution...
=if(C2<>"",ArrayFormula(countif(left($C$2:$Z$18,4),left(C2,4)))>1)
I hope this helps anyone else trying to highlight partial duplicates across multiple columns.