Delete rows based on criteria

[Chris_t_2k5]

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]

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

[John James]

Here's an ad-hoc quick and dirty approach:
1. Backup your file
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. Use a filter to identify all the 3's. Delete them
4. Use a filter to identify all the 5's. Delete them
5. Check you've only got 4's left

If it is a regular need you might consider a macro.

Or if you have other similar needs, 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

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