Count the number of duplicate unique numbers

Ari Cosenza

Hi,

I have a column of unique numbers that I would like to track if duplicate entries occur. If a duplicate entry occurs I would like to pull the unique number into my metrics sheet and track it in a separate sheet; ideally id like the new column in the metrics sheet to include the duplicate unique number as well as how many times it was duplicated.

Any help getting started would be great, I've tried Countifs but without a set range it has been difficult!

SolutionSal

Hello, you could potentially use cross sheet references to compare a list of values with a list of unique values using the MATCH formula. MATCH will throw an error if value isn't found. Wrap a MATCH in an IFERROR to turn the error into a one which will check the box. Then wrap that into an IF statement so any non-error returned value becomes a zero which will uncheck the box.

Formulas:

Cross Sheet References:

You could potentially expand on this to maybe move/copy rows from the List of Values sheet over to the Unique Values sheet. Hopefully this sparks some inspiration!