How to force uniqueness of cell in Google Spreadsheet with non-Latin text in cell?

Question

I have Kannada text in my column and it's not working, but it worked fine with Latin text before. Kannada text is from a region near India. Here is my spreadsheet, there are too many rows to include in this post.

word    frequency   
ಮತ್ತು   71520   
ಈ   40233   
ಒಂದು    25255   
ಎಂದು    17046   
ಇದು 15032   
ಅಥವಾ    14527   
ಹಾಗೂ    13709   
ಅವರು    12979   
ಎಂಬ 10660   
ಮೇಲೆ    10581   
ಅವರ 10204   
ಆದರೆ    9271    
ತನ್ನ    9090    
ತಮ್ಮ    8768    
ನಂತರ    8672    
ಮೂಲಕ    7645    

The validation rule is:

=COUNTIF($A$2:$A,"="&A1) < 2

That is, on the second row onward, validate that no two cells have the same value. I double checked that the cell that has the red flag on it doesn't have a duplicate, and it doesn't, so something must be off with the way unicode values are calculated in that expression or something, any ideas?

I learned how to do this in the first place from Forcing uniqueness in a Google Spreadsheets column

Answer 1

Simple string comparison is case-insensitive, and will also consider certain Unicode glyphs the same even when they look different. For example, the = operator will consider the double-wide character ｅ (code 65349) the same as the normal Latin e (code 101):

• ="ｅ" = "e" gives true

• =exact("ｅ", "e") gives false

The countif() function uses simple comparison operators such as =, and thus considers these values the same, among others:

• ಮೂಲಕ row 17, ಮ‌ೂಲಕ row 3702

• ನ row 26, ‌ನ row 6581

To compare strings more strictly, use exact(), like this:

=rows(filter(A$2:A, exact(A$2:A, A2))) < 2

You can apply that as a data validation rule to the range A2:A, but performance will be abysmal with larger datasets.