repeating number in a data

  • Thread starter Thread starter james
  • Start date Start date
J

james

hiii sir i am working in a excel with 10,000 number of data...... these are
the customers numbers and such time it will be repeated like that
First Name
9936615220
9336963152
9818141525
9798415632
9838810086---repeated
9026929808
9336931125--
9838200296
9336931125--
9838810086---repeated
i want to find and destroy the no.. give me a best way.......thankyou
 
I would add a helper column with a formula like:

=countif(a:a,a1)
and drag down

Then I could filter this column to show the values that are greater than 1 and
delete those visible rows.

This formula will mark each row that is a duplicate -- including the initial
value.

If you wanted to keep that first value, then you could either use
data|filter|advanced filter and use the unique records option.

Debra Dalgleish describes it here:
http://contextures.com/xladvfilter01.html#FilterUR

Or you could modify the formula:
=countif($a$1:a1,a1)
and drag down.

The first value (or only value) will be marked with a 1. So you could still use
that autofilter technique to remove the visible rows.
 
Assume that your data is in A Column and its starting from A1 cell.

If you want to show the duplicate message when a number appears more than
single instance including the 1st instance, then use the below formula in B1
cell and drag it to the remaining cells of B column based on the A column
data.
=IF(COUNTIF(A:A,A1)=1,"NO DUPLICATION",IF(COUNTIF(A:A,A1)>1,"DUPLICATION",""))

If you want to get the duplicate result when the number appears greater than
1st instance, then use the below formula in B1 cell and drag it to the
remaining cells of B column based on the A column data.
=IF(COUNTIF($A$1:$A1,$A1)=1,"NO
DUPLICATON",IF(COUNTIF($A$1:$A1,$A1)>1,"DUPLICATION",""))
 
Back
Top