How to identify duplicate records and count them

hi,

I have 12345 records in a column (cell A2:A12346) There are some
records which are duplicate. I want to calculate how many records are
duplicate and what are the records that are duplicate. For eg., if
country name USA is coming 50 times in my record, I want to get a
display that USA 50 in 2 seperate columns in row adjacent to the
original data.

Regards,
Vishal


---
Message posted from http://www.ExcelForum.com/

One of:

a) Build a pivot table from your data;

b) A formula system:

B1 must house a 0.

In B2 enter & copy down:

=IF((A2"")*ISNA(MATCH(A2,$A$1:A1,0)),LOOKUP(9.99 999999999999E+307,$B$1:B1)
+1,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,B:B)

In C2 enter & copy down:

=IF(ROW()-ROW(C$2)+1=$C$1,INDEX(A:A,MATCH(ROW()-ROW(C$2)+1,B:B)),"")

In D2 enter & copy down:

=IF(C2"",COUNTIF(A:A,C2,"")

c) Invoke Advanced Filter on column A (with a distinctly formatted label in
A1) with Unique records only checked such that the distinct list the Filter
produces start in C1. Then apply the COUNTIF formula from the previous
option.

"vishal " wrote in message
...
hi,

I have 12345 records in a column (cell A2:A12346) There are some
records which are duplicate. I want to calculate how many records are
duplicate and what are the records that are duplicate. For eg., if
country name USA is coming 50 times in my record, I want to get a
display that USA 50 in 2 seperate columns in row adjacent to the
original data.

Regards,
Vishal


---
Message posted from http://www.ExcelForum.com/