CODEWITHSUNDEEP
google-sheets
David Bandel
David Bandel

Reputation: 252

How to Find out if a column contains any duplicates

I have a column of numbers. I want to know if there are any duplicates. I don't need to know how many or what their value is. I just want to know if there are any.

The best way I could figure out was to have another column of equal height to the column of numbers, with the formula:

=countif(A:A,A1)>1

So this will put a TRUE next to every number that has one or more duplicates in the list.

From here I need to see if this second column contains a TRUE.

So I have a final cell with this formula in it:

=lookup(true, B:B)

This always displays FALSE, even when there are duplicates in the list, with corresponding "TRUE" values next to them in column B.

Also, is there a simpler way of solving this problem?

Note: I can get it to work if the single cell result simply does an =OR(B:B) but I still want to know why my first way won't work and if there is an all around simpler way of doing this.

Upvotes: 1

Views: 89

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

you can use both =unique(A:A) and also =counta(unique(A:A))

note: the A:A is just a dummy array i threw in for example, replace with whatever column you want to refer to.

to get a final yes or no, you could nest it together by putting =if(eq(counta(A:A),counta(unique(A:A))),"No Duplicates", "Contains Duplicates")

Upvotes: 1

pnuts
pnuts

Reputation: 59450

I'm not sure whether simpler (I am confident the formula could be simplified!) but copy/pasting the following might be deemed so: 

=sum(if(ARRAYFORMULA(countif(A:A,A1:A)>1),1,0))

This should return 0 only if there are no duplicates. If a single entry is repeated twice (three instances) and all other values are unique, the result should be 3.

TRUE is curious as the behaviour is not what I expected and I differs from Excel where true would be converted to TRUE, which normally indicates an automatic change from text to function. I don't have an explanation but it may be connected with lookup because the boolean behaves as I would expect in say an if formula.

Upvotes: 0

Related Questions