If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
repeating number in a data
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 |
#2
|
|||
|
|||
repeating number in a data
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 he 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 |
#3
|
|||
|
|||
repeating number in a data
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,"DUPLICATIO N","")) -- 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 |
Thread Tools | |
Display Modes | |
|
|