Excel Formula for Duplicates

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.