Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return multiple rows of data based on criteria | Excel Worksheet Functions | |||
Can I delete blank rows from excel without selecting them? | Excel Worksheet Functions | |||
Delete rows based on certain criteria | Excel Discussion (Misc queries) | |||
Code to delete rows and column cells that have formulas in. | Excel Worksheet Functions | |||
Copying whole rows based upon one criteria | Excel Discussion (Misc queries) |