repeating number in a data
in [General]
|
Prev: Help with setting a "nof found" statement
Next: How do i extract worksheet cells with the same contents?
From: james on 20 Apr 2010 20:19 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
From: Dave Peterson on 20 Apr 2010 20:33 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. james wrote: > > 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 -- Dave Peterson
From: Ms-Exl-Learner on 20 Apr 2010 21:21
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","")) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "james" wrote: > 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 > |