I had question in a webinar recently about a formula to list duplicated values. There may be a shorter formula, but I found and shared a solution.
In column A in the image below we have some codes. The last three codes are duplicates of the ones above.
The formula in cell C2 lists the unique entries – easy – there is UNIQUE function.
In practice the UNIQUE function removes duplicates – so the last three entries in the list are ignored.
Cell E2 lists the entries that appear only once. Again, this is easy as UNIQUE has an optional argument to list entries that appear only once.
Cell G2 lists the values that were duplicated. This is trickier.
We combine the two lists (Unique and Appear Once) using VSTACK. Then we use another UNIQUE function to analyse the VSTACK range. We only show the entries that appear once. That should leave only the duplicates listed because there weren’t in the Appear Once list.
There may be a shorter formula but at least it works.
Here is another formula to retrieve the duplicate codes…
=UNIQUE(FILTER(A2:A11,COUNTIF(A2:A11,A2:A11)>1))
Besides saving one character, it is probably “neater” to have the range appear only once, so here is the above formula modified to do that…
=LET(a,A2:A11,UNIQUE(FILTER(a,COUNTIF(a,a)>1)))
Thanks for sharing Rick