identifying rows to delete based on multiple conditions

Maybe this has been taken care of in another thread, but still:

ROW ID NUM OCC K OCC I
1 222222 0 0
2 222222 0 0
3
4 333333 0 0
5 333333 0 0
6 333333 0 0
7
8 444455 1 1
9 444455 1 1

I have a situation like above, but what I want to do is:

=FOR(ROW1:ROW9, IF COUNT ROWS(ID_NUM)>= 2,

(create name for set?)

AND

IF OCC_K AND OR OCC_I VALUES >= 0,

THEN DELETE ROWS(within set) >=2

Or more simply:

If there are rows with multiple Accident ID numbers, name that set,
and if OCC_K and/or OCC_K values are BOTH >= 0,
then delete all duplicate rows?

Yes I figure there needs to be some way to distinguish Row 1 from Row 2, (flagging one of the two?) so that ROW 2 can be identified, and similarly with Row 4 from Row 5 from Row 6, but the general flow of commands is what I'm trying to get at.

Is it also possible to use AND and OR in the same line? Or BOTH?

(I have other variations on this that I also like to ask about, such as multiple rows within a set that meet certain conditions that are identified in a different field, but for now this is a simpler version.)

I would like to think I'm not going to need a macro, and that I can do this in a Formula.

Thanks kindly,

Dan B

You have to delete rows backwards or the rowcount screws up...
use:

Please Login or Register  to view this content.

Ranman256:

Thank you for getting back to me so quickly, but until I hear otherwise, I don't know if I can use a VBA macro or script.

I also don't know what you mean by "delete rows backwards or row count screws up?" I should mention as well that the files I am working with have blank rows between each set of duplicates. Should I delete all the blank rows or can something like what you are posting work on what I've got?

I can tell you that the Range is the number of rows with the same ACC ID (i.e., the number of duplicates). Would this help?


Dan

I've attached a basic spreadsheet.

For background:

The database covers Traffic Accidents. The Severity of the Accident needs to be determined first (click on cell E2 for the formula that in part determines the severity).
For my sake:

IF the there is a duplicate set (2 records or more) of values (i.e. 0 or more killed and 0 or more injured), like what I showed in my initial post, then what I want to do is delete the second record, and if there are more than 2 records, delete all other records if they have the same values (i.e., 2 or more other sets of 0 killed 0 injured).

I will clarify more soon.

Dan B

Sorry. Here is the database. Will clarify more if needbe on Friday PST in U.S.

Dan B

Sorry, i was deleting the rows. You just wanted to paint them. So going backwards is irrelevant.

FDibbins,

Thank you kindly for your efforts; this was helpful (with a small tweaking--for now, my supervisor just wants to know What to Do when we have 2 or more sets of 0,0). When I selected all the sets that have 2 or more 0,0 and copied them over to another Sheet, if I unchecked everything not = 1, I got exactly what I was looking for.

I will say that on the main sheet, when I dragged your formula all the way down, I saw the Green Triangle pop up in the corner of a good number of cells, which then led to a message of "The formula in this cell refers to a range that has additional numbers adjacent to it."

When I did the same thing with the sheet of just the sets of 0,0, I didn't have this problem.

Anyhow I have a sense this might work.

FDibbins,

For starters, your formula works well, but there is a problem. See the attached file with the comments.

Yes I admit I probably should have put in the basic scenario that I have at the top, because this helps clarify which ACC_NUM out of the set of duplicates to keep, but what I am wondering about mainly is if there is a way of extending the formula that you have to a situation like:

after unchecking everything = 0 or > 1, if there are two ACC_NUM records = 1, which one to delete?

Or: is there a way to have the formula you have linked to the SEVERITY scenario on top? Can I have "1"s (in text format) be treated in the same way as numbers would be?

For example with 3548948, both 2-1 and 2-0 will make it a K, but is there a way to have the formula compare one of the 2-1s vs the 2-0?

Dan

FDibbins,

Even if we correct this error, it still may not give me the end result I want, but at the same time, why is it I am getting an error of some kind (little green triangle) for certain records and not others after I copy-paste your formula?

What would you think is causing this?

Dan