# 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