11

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
18

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

| improve this answer | |
3

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][0];
    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.

| improve this answer | |
  • var values = {} needs to be var values = [] right? – Jacob Jan Tuinstra Aug 11 '15 at 16:17
  • 1
    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
  • 1
    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
  • 1
    I meant object. – Jacob Jan Tuinstra Aug 11 '15 at 21:10
  • 1
    @JacobJanTuinstra Ah, yes - I see that now. Corrected the code. – Vidar S. Ramdal Aug 11 '15 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.