Data Validation: how to ensure all columns have unique data within the column

Question

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.

Answer 1

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.

Answer 2

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.