Flagging/Preventing Duplicate entries in a Google Sheet

Question

I've looked for a while now and can't quite find a way to perform the task I am attempting to in Google Sheets.

I am trying to prevent, either through data validation via custom formula or script, preventing the possibility of entering duplicate data in cells within my Google Spreadsheet. A few things to note before you provide an answer:

• I need the cells belonging to each of these columns to have a character limit (first column should have 7 character limit, next column should have 17 character limit).

• Each cell will contain alphanumeric characters.

• I need replication of segments of data within each cell to be possible, but replication of all data within the cell to be prohibited (ex. "G123123" and "G123124" should be allowed, but not "G123123" and "G123123".

I've found some things that come close, but don't quite reach the resolution I need. Before anyone suggests using a database, please understand I'm working with technologically-challenged individuals that will not allow usage of a full database at this juncture.

These forms of data entry should be allowed (partial but not entire duplication of previously entered data):

H123124 1234567890ABCD
H123125 1234567890ABCE
H123126 123ABCD4567890

Notice: The above rows of data contain some matching characters, but not complete duplicates. These need to be allowed.

Edits:

To clarify, I'm looking for a script, or closely related script (or custom formula) that will limit characters allowed in a cell. I'm also looking for the same script or formula to not allow entirely duplicate entries in each cell. In essence, I'm looking for a way to not allow entry of duplicate data in each cell of a column, but still allow some repeating characters, as long as an entire string of data is not duplicated.

Here is a link to a spreadsheet with comments explaining what should be allowed and what shouldn't: https://docs.google.com/spreadsheets/d/1Gohyu20P_ZBlGxDwL5kr-7BymW_wY5IZ_agzFqUGiSY/edit?usp=sharing

Answer 1

I decided to use a simpler methodology to collect similar results to resolve my issue. A custom formula must be entered under conditional formatting rules in google sheets in order to achieve this goal. Moreover, you still must select an "Apply to range:" for the columns/rows you wish to affect. The following formula can be used to highlight duplicates in google sheets across multiple columns where necessary:

=COUNTIF($A$2:$B$40, INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1

If you're interested in using the same formula, make sure you alter your row/column selection within the following part of the formula:

($A$2:$B$40,

This method will not truly prevent users from entering duplicate data, but will make it painfully obvious when duplicate data is entered. I'm still working on the character limitation, though that isn't as large of an issue for us.