duplicate records, how to identify and count

  • Thread starter Thread starter vishal
  • Start date Start date
V

vishal

Hi ,

I have got stuck in this very cumbersome problem. I have downloade
around 12000 records in a cell. From cell A1 to going down to cel
a12043. However, there are certain duplicate records in those 12000
records.

My problem is how to identify how many records are there and how t
check for eg. a record ABC Inc. appears how many times.

thanks
Visha
 
You can get a count of unique records with:

=SUMPRODUCT(1/COUNTIF(A1:A12043,A1:A12043))

To see how many duplicates their are of each record, you
could put this in B1 and fill down:

=COUNTIF(A:A,A1)

HTH
Jason
Atlanta, GA
 
Hi
just on a sidenote: this will count blank cells also. To omit blank
cells try
=SUMPRODUCT((A1:A12000<>"")/COUNTIF(A1:A12000,A1:A12000&""))
 
Back
Top