Thread: delete row based on multiple conditions

Hi,
i was wondering if anyone can help!

I'm not very good at writing code with Loops so I need someone to pen something for me that meets the following:

The attached spreadsheet has 1000's of rows of data, and has been sorted by column B and then by column A. This allows me to see whether an outage has occurred on a host. I can see this because column D will show a "Critical" alert, and when the host is back up again, it will show an "OK" alert.

This is confirmed when you look in column G - where the message will state "Unable to Ping device - Check availability" when the host goes down, and when its back up again, the message will state "Connectivity has returned"

the problem is there is a lot of data in here that doesnt have a matching downtime message against an uptime message.

what i need is a piece of code that looks at the device name in column C, then checks for a 'critical' status. The next check should be whether there is an 'unable to ping device' message (but happy for this to be a key word condition (i,e, column G contains the word 'Availability'

Once that condition has been met, it needs to look at the next row - and identify an identical host name with a corresponding status of OK. Then to validate, column G needs to have a 'connectivity has returned' message.

If these conditions are met, then we are good. In theory, the date/time field should be within minutes of each other (but this doesnt need to be part of the check, as the sort should have taken care of this)

So then it loops onto the next row and tests the same conditions - until it finds a condition that doesnt meet the above - which then it should delete the row.

then carry on with the next row.

Once finished, column D should look like - Critical, Ok, Critical, Ok, critical, ok..... etc etc as you look down the column.

I can do this manually, but its taking FOREVER, so would appreciate it if someone could automate this for me.

Thanks in adnvance.

Aaron

If the goal is to remove the unneeded CRITICAL events only, then you don't need a macro.

1) K1: "Key"
2) K2: =IF(AND(D2="CRITICAL",ISNUMBER(SEARCH("Unable to Ping Device",G2))),AND(C3=C2,D3="OK"), TRUE)
3) Copy K2 down through K4803

4) click K1 and turn on the Data > Filter > Autofilter
5) Filter column K for FALSE
6) Delete all visible rows
7) Turn off autofilter

Of course, you could record all those steps into a macro pretty easily.

Well, that will take care of the extra critical lines. Please give full explanation of how to discern an unneeded OK line.

Hi - That works well for the criticals - thanks for that.

the next stage is to remove the "OKs" that dont have a matching Critical.

So for each host, there should be CRITICAL and a matching OK, so can you set up a rule that checks the host, and then checks the severity for a CRITICAL, then should have a matching OK message for the same host (usually within minutes of the CRITICAL severity).

this should in theory remove all the unessary OK severities in the spreadsheet - leaving us with the dowtime and corresponding uptime message?

makes sense?

I was thinking perhaps the tests would be even simpler.

Ultimately you indicated you were looking to create a list of the pairs of CRITICAL/OK. Since the data is sorted, they should appear in the group together.

So once you've happily removed the extra "CRITICAL"s, then wouldn't we just be removing extra OKs where the line above was not CRITICAL for the same host? And/Or any OK that has an "unable to ping" message?

There are now a couple of options in K & L for you to filter by TRUE, each gives slightly different results.

AutoFilter column K by TRUE and you "mostly" see the paired items you want. Some CRITICAL items do not have a subsequent OK, like row 169, row 191, row 551. Some are just odd like row 381, where it's CRITICAL, but the message is "Connectivity has returned".

The column L version resolves those, but may result in others being hidden you don't want hidden. There are some 'straggler" entries that have no paired items at all, just a single entry. K would show those, L would hide them.