2

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

1 Answer 1

4

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" = "e" gives true

  • =exact("e", "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.

2
  • Oh wow it took about 3 minutes after saving that data validation like that for the spreadsheet to update. Slow like you're saying. Is there a better way, would I need custom JavaScript? Actually yeah it crawls when updating, it takes over a minute to update, not real-time like I was expecting.
    – Lance
    Nov 14, 2023 at 14:03
  • 2
    That depends on what you are trying to accomplish. Your exact requirements remain unclear. If this is a data-entry system, you may be better off by validating each entry just once — at the time it is entered. You do not need to check it again and again after that. Ask only one question per post. Post a new question instead. Nov 14, 2023 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.