AUTOMATIC DELETION OF SPECIFIC ROWS?? 1

[Edmee]

I have a large spreadsheet (> 5000 rows) where I need to delete certain rows. For example:

row1 Server1\directory1row2 Group Users has rights
row3 Server1\directory2row4 Group Users has rights
row5 Server1\directory3row6 Server4\directory1row7 Group Users has rights
etc.....

Now I need to delete the rows that contain ONLY the server entries NOT followed directly by the "Group Users has rights" row. For instance, I would need to delete row 5 "Server1\directory3\" entry in the sample list above.
This could be just 1 row or in other cases 2 or 3 consecutive rows. How would I go about doing this automatically?

 

[JVFriederick]

Set cursor on first record, select Data-Filter-AutoFilter.
Then click on pulldown and select Custom.

The dialog box will allow to SELECT records or NOT SELECT records.

For example "Does not begin with" ..... "Group Users" may give many of the records to delete. (the keyboard can be used to type in special matches) Highlight and then delete rows.

When filter is then turned off (repeat first step), what remains are the desired records.

 

[JVFriederick]

Whoops. Didn't read carefully enough ! Sorry.
Assume all the records are in column A and start at row 1.
In column B, enter this equation =left(A1,6)&left(A2,6)

This will create a "Selection Key" you can use.

Copy all formulas in column B and paste as values (this will preserve the relationships after deleting records).

I think the ones that return "ServerServer" will be the ones you wish to delete.

 

[Edmee]

Thank you, thank you! That worked wonders... Phew, you saved me a major headache.

 

[JVFriederick]

Nice to know it helped.