DELETING CELS
 

I HAVE A LIST OF SOME 20,000 NAMES. SEVERAL OF THE NAMES ARE LISTED MORE THAN
ONCE. I NEED TO DELETE THE ONES THAT ARE LISTED LESS THAN 10 TIMES.
HOW DO I DO THAT?
THANKS!
CHRIS

What I'll do is the following:

Put the names in Column A and sort them.

Column B put the following Formula

=IF(A2=A1,B1+1,1)

Then go to DataFilterAutofilter

When you have the filters, Select Custom and select "is less than"
and in the rigth put 10 OK

Select all the range and then delete it.

Release the Autofilter.

Select All data Again and sort it again to have all the data together
and eliminate the deleted rows.

Saludos

NlCO


--
NlCO
------------------------------------------------------------------------
NlCO's Profile: http://www.excelforum.com/member.php...o&userid=26123
View this thread: http://www.excelforum.com/showthread...hreadid=402066

"Chris" wrote:
( with all caps converted )
I have a list of some 20,000 names.
Several of the names are listed more than
once. I need to delete the ones that are listed
less than 10 times.
how do i do that?

As it stands*, one play ..

Assuming the names are listed in A1:A20000

Put in B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)<10,"",ROW()))

Put in C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A ,MATCH(SMALL(B:B,ROWS($A$1
:A1)),B:B,0)))

Select B1:C1, fill down to C20000

Col C will return the residual list that you're after,
with all the results neatly bunched at the top

*I'm not sure whether you have a typo in the line:
.. delete the ones that are listed
less than 10 times.

If however what you really want? is to extract
the list of unique names in col A,
just replace the formula in B1 above with:

=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW()))

(Formula in C1, and rest of the steps unchanged)

Btw, please remove the caps lock when you type your post.
All caps is awfully tough to read and is considered impolite ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

And if desired, just select and kill all formulas in cols B & C with
an in-place copy paste special check "values" ok
Then clear or delete col B
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--