0

Is there any way to find out, with the help of a formula, whether all elements of a certain range are unique or not without having to program a macro in VBA?

Let us say that the elements in column A are K1 in cell A1, K2 in cell A2 and so on. I now want to check whether all of the elements in column A are unique or not. If they are unique, return True in cell B1, if they are not, return False.

It would be great if this could also work for checking rows so that a formula can, for instance, find out whether all elements in row 1 are unique or not and return True if they are and False when they are not.

1
  • yeah sure there, show us an example of your range
    – PeterH
    Aug 21, 2020 at 9:42

1 Answer 1

4

You can use this formula (adjust range appropriately):

=COUNTA(A1:A4)=COUNTA(UNIQUE(A1:A4))

enter image description here

To ignore blanks, you can filter them out before using UNIQUE. Like this:

=COUNTA(A1:A4)=COUNTA(UNIQUE(FILTER(A1:A4,A1:A4<>"")))
5
  • Ok, this is very good! Is there a way to exclude empty cells from the range? If, for example, A2 and A3 are empty and A1 contains K1 and A4 contains K4, return True?
    – Nemgathos
    Aug 21, 2020 at 10:23
  • 1
    Please see edit. Aug 21, 2020 at 10:54
  • Do you also know how this works when checking elements of a certain row? =COUNTA(A2:D2)=COUNTA(UNIQUE(FILTER(A2:D2,A2:D2<>""))), for example, does not seem to work analogously.
    – Nemgathos
    Aug 21, 2020 at 11:50
  • In that case I believe you would need to wrap both ranges inside the FILTER function with TRANSPOSE Aug 21, 2020 at 12:39
  • 2
    @Nemgathos switch the UNIQUE to columns: =COUNTA(A2:D2)=COUNTA(UNIQUE(FILTER(A2:D2,A2:D2<>""),TRUE)) Aug 21, 2020 at 13:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .