0

I've a working custom formula put in the data validation box in column A in a Google spreadsheet to disallow entering duplicates in the same column A.

=countif($A$3:$A$1677,A3)<=1

Now i have another column, column B, and i want the above formula to be moved in column B from column A editing it in such a way that it offers the following :

+---+------------+-------------+
|   |     A      |      B      | <-- the custom formula to be kept in column B only (in the data validation box).
+---+------------+-------------+
| 1 | John       | MySQL       | <-- if this row/data is already existing, then
| 2 | John       | Oracle      | <-- this entry is allowed (bcoz the data in this row do not match with data in the above row (considering both columns), and
| 3 | Viv        | MySQL       | <-- this entry is also allowed (bcoz the data in this row also do not match with data in the above two rows (considering both columns), and
| 4 | Al         | SQL         | <-- this entry is also allowed (bcoz the data in this row also do not match with data in the above three rows (considering both columns), but,
| 5 | Viv        | MySQL       | <-- this entry is NOT allowed (bcoz the data in this row entirely match with data in one of the above rows - row no. 3 (considering both columns).       
+---+------------+-------------+

I have modified the aforementioned formula a little as below :

=countif($A$3:$B$1677,B3)<=1

But it does not fulfill the 2nd condition (Viv, MySQL) stated above.

viv227295
  • 387
  • 2
  • 6
  • 17

1 Answers1

2

Try this:

=COUNTIF(ARRAYFORMULA($A$3:$A$1677&$B$3:$B$1677),A3&B3)<=1

Instead of checking each column separately, you just join the values together and check to see if more that one of joined string exists!

Hope that helps!

Jack Brown
  • 5,802
  • 2
  • 12
  • 27