# Extend UNIQUE() for distinct values that appear more than once

Excel’s Unique() function itself can’t show distinct values that appear more than once in a list. Repeated only items in a distinct list are possible with a simple extended formula.

We’ve already shown the basics of Unique() either getting a list of Distinct values or values that only appear once aka Unique.

But Unique() doesn’t directly handle values which appear more than once in the list.  In other words, all a Distinct list (Col E) but without the Unique (once-only, Col G) items.

In a transaction list you might call them repeat customers or repeat products.  In Column K, see the products that have been bought more than once.

Unique() can’t do this directly, maybe it should for the sake of completeness but the workaround is quite simple.  Just add Filter() to the list before getting Unique values.

The filtered array formula is this:

`FILTER(<SourceArray>,COUNTIF(<SourceArray>, <SourceArray>)>1)`

This makes a list of all the items which appear more than once.  In other words, the list is filtered with CountIF to eliminate anything that appears only once.  Of course you could change the “ >1 “ to any test you like such as “ >10 “ to list only items that appear more than ten times.

That formula produces a list/array like this:

All that’s needed is Unique() around that formula to remove the duplicates:

`=UNIQUE(FILTER(Products[Customer], COUNTIF(Products[Customer], Products[Customer])>1))`

The default values for Unique are enough, no additional parameters are necessary.

## Counting the Repeats

We added the count of items, just to make the purpose of the list clearer.

That’s a simple Countif() for each item in the Column K dynamic array.

`=COUNTIF(Products[Customer],K3)`

That formula isn’t a dynamic array. It will have to be manually changed if the list changes.

#### Office-Watch.com

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.

Office 2021 - all you need to know. Facts & prices for the new Microsoft Office. Do you need it?
Office LTSC is the enterprise licence version of Office 2021.

Office 2024 what's known so far plus educated guesses.

Microsoft Office upcoming support end date checklist.