A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

repeating number in a data



 
 
Thread Tools Display Modes
  #1  
Old April 21st, 2010, 01:19 AM posted to microsoft.public.excel.misc
JAmes
external usenet poster
 
Posts: 904
Default 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  
Old April 21st, 2010, 01:33 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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  
Old April 21st, 2010, 02:21 AM posted to microsoft.public.excel.misc
MS-Exl-Learner
external usenet poster
 
Posts: 135
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.