I have this column in Google sheets with 11 digits of code that is supposed to be a unique per row. The first 3 digits are the department location, and the last 8 digit is the batch number and it has to be unique.
sample code:
G0420220001 and K0020220002
I want to make sure that the last 8 digits are unique per row.
In Conditional Formatting, without the first 3 digits, I can use this custom formula
=countif(b:b,b:b)>1
to highlight duplicates, and the 20220002 entry in row 1 will be highlighted if I input another 20220002 in a different row, but K0020220002 and G0420220002, which will appear to be unique at each other will not be highlighted.
I want that these two codes to be highlighted since the last 8 digits of both entries are the same.
Add a comment
|
1 Answer
Welcome!
It seems to me that this formula will do the job:
=ArrayFormula(COUNTIF(RIGHT($A$1:$A,8)*($A$1:$A<>""),RIGHT(A1,8))) > 1
