How to identify duplicate records and count them

[vishal]

hi,

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

Regards,
Visha

 

[Aladin Akyurek]

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.99999999999999E+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.