Delete rows based on criteria
 

Here is my problem.

I have a spreadsheet with columns A:G populated. Within column A are various
names of dogs. Columns B:G are statistics about the dogs.

What I require is a macro that will delete certain rows based on the
following criteria: In column A there are many duplicate names. I only
require 4 of each name so if there are 5 instances of "Annie" then 1 should
be deleted and if there are only 3 instances of "Bob" then these should all
be deleted as there are not 4.

Thanks in advance

Delete rows based on criteria
 

I'd insert a helper column near column A (a new column B???).

Then put this formula in B2 (headers in row 1???):

=IF(COUNTIF(A:A,A2)<4,"DeleteMe",IF(COUNTIF(A2:$A$ 2,A2)4,"DeleteMe","KeepMe"))

Drag down the column

Apply Data|Filter|autofilter to this helper column
and show the DeleteMe rows

Delete those visible rows.
data|filter|show all (to see everything)
Double check your work

And delete the helper column



Chris_t_2k5 wrote:

Here is my problem.

I have a spreadsheet with columns A:G populated. Within column A are various
names of dogs. Columns B:G are statistics about the dogs.

What I require is a macro that will delete certain rows based on the
following criteria: In column A there are many duplicate names. I only
require 4 of each name so if there are 5 instances of "Annie" then 1 should
be deleted and if there are only 3 instances of "Bob" then these should all
be deleted as there are not 4.

Thanks in advance

--

Dave Peterson

Delete rows based on criteria
 

Here's an ad-hoc quick and dirty approach:
1. Backup your file in case you get unexpected results
2. In row 2 of the next available column enter
=COUNTIF($A$2:$A$100,A2)
(assuming your table goes to row 100, + put appropriate heading in row
1)
Copy this formula against each row
3. Ensure you have a field which you can sort on to get back in the
same order (if important)
4. Sort the table based on the new column created
5. You can then delete the 3's and 5's and leave the 4's.
6. Sort back if necessary

If it is a regular need it would be better to set up a macro.

Or use John Walkenbach's excellent utility below which deletes rows
based on specified criteria. If you go this way, say so and I or
someone else will give you formula to work with that utility.

http://www.j-walk.com/ss/excel/files/rowdel.htm

Chris_t_2k5 Wrote:
Here is my problem.

I have a spreadsheet with columns A:G populated. Within column A are
various
names of dogs. Columns B:G are statistics about the dogs.

What I require is a macro that will delete certain rows based on the
following criteria: In column A there are many duplicate names. I only
require 4 of each name so if there are 5 instances of "Annie" then 1
should
be deleted and if there are only 3 instances of "Bob" then these should
all
be deleted as there are not 4.

Thanks in advance


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531860