# How can I find duplicates in a column, using formulas?

Has anybody come up with a formula to find duplicates in a column? For example:

a
b
c
d
b
d


=somefunction(A1:A6)

Output:

b
d


I know there is a =unique() function, it's basically a =not_unique() function. Was wondering if it can be constructed without using code.

• Here is one example in this thread stackoverflow.com/questions/19843406/… have a look at this which could get you with some idea. – Vembu Aug 11 '15 at 8:03
• Wait, what you're looking for is a not_unique function right? You seem to be looking for the fields that are not unique (the duplicates), not the fields that are unique. – David Mulder Aug 11 '15 at 14:14

The possible solution without an additional column is:

=FILTER(UNIQUE(A2:A7), ARRAYFORMULA( COUNTIF(A2:A7,UNIQUE(A2:A7))>1 ))


Please, see prepared example here: Find Duplicates

The only way I found (using formulas) is by introducing a new count column.

If your values are listed in column A, starting on row 2, enter the formula =COUNTA(FILTER(A$2:A$7, A$2:A$7 = A2)) in B2 and drag down to copy it. This gives:

| A: VALUE  | B: COUNT |
|-----------|----------|
|     a     |     1    |
|     b     |     2    |
|     c     |     1    |
|     d     |     2    |
|     b     |     2    |
|     d     |     2    |


Now that we have a count of occurences of each value, we can filter the A column by values from B. In cell C2, insert the formula =UNIQUE(FILTER(A2:A7, B2:B7 > 1)). This gives:

| A: VALUE  | B: COUNT | C: DUPS |
|-----------|----------|---------|
|     a     |     1    |    b    |
|     b     |     2    |    d    |
|     c     |     1    |         |
|     d     |     2    |         |
|     b     |     2    |         |
|     d     |     2    |         |


# Explanation of formulas

## =COUNTA(FILTER(A$2:A$7, A$2:A$7 = A2))

• The filter clause inspects the A column, and finds cells which have the same value as cell A2 (A2 is replaced with the corresponding cell when copying the formula).
• counta counts values (including non-numeric).

## =UNIQUE(FILTER(A2:A7, B2:B7 > 1))

• This filter examines column B for values that are > 1, and returns the corresponding values from column A. The unique function simply makes sure we only return each value once, so we don´t get b twice, for example.

However, a more elegant way would be to use a script function:

function dups(rows) {
var values = {};
var duplicates = [];
for (var i = 0; i < rows.length; i++) {
var value = rows[i];
if (values[value] !== undefined && duplicates.indexOf(value) == -1) {
duplicates.push(value);
} else {
values[value] = true
}
}
return duplicates;
}


Go to Tools → Script Editor, paste the above code, and save. You can now call the function by entering the formula =dups(A2:A7) anywhere. This returns the duplicates found in A2:A7.

I have set up an example spreadsheet to demonstrate both possibilites, feel free to look at it and copy it.

• var values = {} needs to be var values = [] right? – Jacob Jan Tuinstra Aug 11 '15 at 16:17
• Actually, no. values is used as a key-value map, not an array, so that it is easy to check if we have processed a specific value already - either the values[property] exists, or it doesn't. – Vidar S. Ramdal Aug 11 '15 at 16:21
• I thought you were pushing into an array.... I tested your code and it keeps showing duplicate entries, as in d,d,d,d.... The formula solution you provided doesn't do that. – Jacob Jan Tuinstra Aug 11 '15 at 17:01
• I meant object. – Jacob Jan Tuinstra Aug 11 '15 at 21:10
• @JacobJanTuinstra Ah, yes - I see that now. Corrected the code. – Vidar S. Ramdal Aug 11 '15 at 21:11