4

I finally found this answer which tells how to use data validation to make sure all the values in one column are unique.

But it doesn't tell if there is a way to write a single validation rule that can make sure that all the values in multiple columns are unique per column. So far the only way I see is to replicate the validation rule for each (of 50 or so) columns individually.

Is there a way to do that with a single rule? It would hinge on where to put the $, I suppose, and the rules for doing that don't seem to be well documented, especially how they get interpreted inside validation rules.

2

Well, after a bit of fiddling, I found a solution that currently works:

Specify the cell range as desired: for the whole columns, starting with column A and ending with column BB, so it is

A1:BB

Then the custom formula should be:

=countif(A$1:BB,"="&A1)=1

It seems like the Data Validation operation the places this "validation formula" (which must be True to avoid a warning) in every cell, as if a regular formula were copy/pasted to the cell. Which might allow a bit of intuition as to when and how to place the $ in the cell references, if true. It seems as if the formula should be written as if for the top left cell of the range specified.

1

You can also use indirect and address to dynamically refer to each of the columns in the evaluation.

=countif(indirect(SUBSTITUTE(ADDRESS(1,column(),4), "1", "")&":"&SUBSTITUTE(ADDRESS(1,column(),4), "1", "")),A1)=1

indirect is used to point to a cell reference that is in the string format - and you are correct in your comment, that basically the address portion is telling it to work on the current column so in your case every column in the range your specifying - the substitute is just to translate the column addresses back to a string type , in other words column letters instead of integers pointing to row and column number - and lastly the 4 is to keep the current column a relative reference -

I know it looks a lot more complicated than it is because it is a long formula - but it is very useful in many other ways once you get a good handle on how it works.

  • Well, I have no intuition as to how one of those evaluates to column A and one to column BB. Nor what the 4 means. Or maybe they are both supposed to evaluate to the current column? I wonder if there is good enough documentation to understand indirect, SUBSTITUTE, and ADDRESS to figure out what all that means, when the documentation for Data Validation formulas is so sparse? Anyway, it will be a nice research project when I get time, to try to figure out what this means. – Victoria Jun 10 '16 at 22:52
  • @Victoria I updated the answer to try to give you a little more context – Aurielle Perlmann Jun 10 '16 at 22:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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