Google Sheets: Data Validation - Unique row values across multiple columns

Question

Good day,

I have seen from here a solution to control duplicate entries into a single column. A Data validation with this custom formula works well for one column.

I would like to achieve the same effect over multiple columns ... i.e. unique row entries across multiple columns. Take for example below three columns A-C. Only when values {1,2,1} are entered for the second time will the input be rejected.

A          B          C
1          1          1
1          2          1
1          2          2
2          2          2
1          2          1   X Entry should be rejected.

Is there a quick way to do this using Data Validation - custom formulae?

question from:https://stackoverflow.com/questions/65933189/google-sheets-data-validation-unique-row-values-across-multiple-columns

Answer 1

use custom formula for data validation:

=INDEX(COUNTIF($A$1:$A&"×"&$B$1:$B&"×"&$C$1:$C, $A1&"×"&$B1&"×"&$C1)<2)