Compare numbers

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

I am using excel 2004 and have a column with many simple numbers [1045,
1234, 5432. etc.] What is the easiest way to identify duplicates, or the
overall number of duplicates in the column? there must be an easy way.

Thank you !

Alan
 
Alan

select the column, for example, column A. Then select

Format | Conditional formatting ... | Condition 1 | Formula is:
=COUNTIF(A:A,A1)>1
and choose an appropriate format, Format ... | Font | Font style = bold, for
example

If you need to count them as well as highlight them, you could use an
advanced filter to select the unique entries from the list and then use a
COUNTIF similar to the statement above to count the values with one or more
duplicates.

Regards

Trevor
 
the overall number of duplicates in the column [...]

=SUMPRODUCT((Range<>"")-(Range<>"")/COUNTIF(Range,Range&""))

This gives you Number of Tokens minus Number of Types in Range where empty
cells and formula-blanks are excluded.
 
Back
Top