I’m working on a Google Sheets project where I need to highlight cells with partial matches across several columns and rows. The data includes bus numbers and fuel readings. For example:
2345
2178
2345/92
I want to highlight cells that have matching bus numbers (first 4 digits) regardless of the fuel reading.
I’ve tried this formula in conditional formatting:
=COUNTIF(INDIRECT("D3:AA19"), LEFT(D3,4)&"*") > 1
But it’s not working correctly. It highlights all cells in the range instead of just the partial matches.
Any suggestions on how to fix this or a better approach to achieve what I’m looking for? Thanks for your help!
I’ve dealt with a similar issue in my work tracking inventory across multiple warehouses. Your approach is on the right track, but it needs a small tweak. Instead of using COUNTIF, try using ARRAYFORMULA with REGEXMATCH. You can try a formula like =ARRAYFORMULA(SUM(REGEXMATCH(D3:AA19, “^”&LEFT(D3,4)))>1) which checks for matches at the beginning of each cell by comparing the first four digits. Make sure your conditional formatting rule is set to ‘Custom formula is’ and adjust the range as needed. Hope this helps solve your issue!
I’ve encountered this challenge when managing fleet data. Your method is close, but COUNTIF isn’t ideal for partial matches across ranges. Consider using REGEXEXTRACT with QUERY instead. Try this formula:
=ARRAYFORMULA(QUERY(REGEXEXTRACT(D3:AA19, “^\d{4}”), “SELECT Col1, COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY Col1 HAVING COUNT(Col1) > 1 LABEL COUNT(Col1) ‘’”))
This extracts the first four digits, then counts occurrences. Apply it to your conditional formatting rule, adjusting the range as needed. It should highlight only cells with matching bus numbers, ignoring fuel readings. Let me know if you need further clarification.