How to identify duplicate records and count them

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

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
 
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.
 
Back
Top