Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Chris_t_2k5
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
John James
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
return multiple rows of data based on criteria steve_sr2 Excel Worksheet Functions 8 May 20th 23 07:47 PM
Can I delete blank rows from excel without selecting them? rgtest Excel Worksheet Functions 9 February 14th 09 04:12 PM
Delete rows based on certain criteria Coal Miner Excel Discussion (Misc queries) 2 March 3rd 06 06:56 PM
Code to delete rows and column cells that have formulas in. GarToms Excel Worksheet Functions 1 January 18th 06 02:04 PM
Copying whole rows based upon one criteria kirbster1973 Excel Discussion (Misc queries) 2 May 26th 05 10:00 PM


All times are GMT +1. The time now is 11:39 PM.

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

About Us

"It's about Microsoft Excel"