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.
Unique filter in Excel – the manual ‘one-off’ options
Three ways to make a unique list in Excel, the hard way and the new way.
Great and simple ‘live’ sorting with filtering in Excel
Better live sorting in Excel 365
Easy and better lists with Excel 365’s Filter()
