Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I keep from repeating a number in the same column? | Excel Discussion (Misc queries) | |||
how do i check if a number is repeating | Excel Discussion (Misc queries) | |||
Repeating Number | Excel Worksheet Functions | |||
random number without repeating? | Excel Worksheet Functions | |||
Random Number Non Repeating ... | Excel Discussion (Misc queries) |