List duplicate values with FILTER

Abstract

In this video, we’ll look at how to list duplicate values.

In other words, values that appear more than once in a set of data.

How can we list names that appear more than once, along with a count?

Now, forcriteria, we want exactly the same list.

As you might guess, we can use a list like this with theFILTER function.

I’ll first copy the COUNTIF formula to the clipboard, then I’ll delete the formula.

In cell G5, I’ll enter the FILTER function.

Forarray, we want the full list of names.

When I enter the formula, we see only names that appear at least twice in the data.

Now let’s generate a count for each name in the list, again using the COUNTIF function.

As before,rangeis the full set of names in the source data.

Now we have a count for each name.

Finally, let’s adjust the FILTER function to use a variable count, instead of a hardcoded count.

In cell H2, I’ll enter the number 2.

Then I’ll modify the FILTER formula to use H2 instead of a hardcoded number.