Multiple line deletion in excel

S

SafariQn

Is there a way to do a search in an excel spreadsheet for a specific 'word'
and have all pertinent lines deleted? I have a spreadsheet of over 11,000
lines and need to delete all lines that contain a specific word - doing this
manually is outrageous. I tried conditional formatting, but this does not
work. Help would be appreciated!
 
M

Max

One way using non array formulas ..

Put in B1:
=IF(A1="","",IF(ISNUMBER(SEARCH("specific_word",A1)),"",ROW()))

[ replace: specific_word with your actual word within the quotes ]

Put in C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",
INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1:A1)),B:B,0)))

Select B1:C1, copy down til the last row of data in col A

Col C will return only the lines in col A which do not contain the
"specific_word", all lines neatly bunched at the top

Adapt to suit ..

Just replace SEARCH with FIND in the formula in B1
if you need the search to be case sensitive (FIND is case sensitive)
 
M

Max

Oops, missed out the assumption line:
Source data is assumed in col A , from A1 down
 
G

Gord Dibben

Could you use the Data>Filter>Autofilter to find those rows with the specific
word then delete those visible rows.

Select the filtered rows and F5>Special>Visible cells only>OK

Then Edit>Delete>Entire Row.


Gord Dibben Excel MVP
 
M

Max

My spreadsheet is very large (to say the least)
over 10,000 line down and through Z across. The
'word' would be in column "U".
What I'm trying to do is delete all the lines that
have this specific word in it.
Click to expand...

To suit your set-up (as you have now clarified above),
try this ..

Assuming your source data is in Sheet1, in A1:Z10000 (say),
and col U is the col to be searched for the specific word

Using an adjacent col to the right of the data, say col AA?

Put in AA1:
=IF(U1="","",IF(ISNUMBER(SEARCH("specific_word",U1)),"",ROW()))
Copy AA1 down as far as there is data in col U

In a new Sheet2
---------
Put in A1:
=IF(ISERROR(SMALL(Sheet1!$AA:$AA,ROWS(Sheet1!$A$1:A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$AA:$AA,
ROWS(Sheet1!$A$1:A1)),Sheet1!$AA:$AA,0)))

Copy A1 across to Z1, fill down to Z10000
(cover the same range as in Sheet1)

Sheet2 will return the desired results, i.e. only those lines w/o the word
in col U in Sheet1, all neatly bunched at the top

If required, kill the formulas in Sheet2 with
an in-place copy > paste special > values > ok
 
M

Max

Copy AA1 down as far as there is data in col U

should read:
Copy AA1 down as far as there is data in col U, i.e. down to AA10000
Click to expand...

(as it's assumed source data is in A1:Z10000)
 
S

SafariQn

Max - entered this formula on sheet 1:
=IF(U1="","",IF(ISNUMBER(SEARCH("Attorney",U1)),"",ROW(U1:U11019)))

Entered this formula on sheet 2: line A1
=IF(ISERROR(SMALL(Sheet1!$AA:$AA,ROWS(Sheet1!$A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$AA:$AA, ROWS(Sheet1!$A$1:A1)),Sheet1!AA:$AA,0)))

Nothing happens.... filled as per your post - but nothing

Linda
 
M

Max

=IF(U1="","",IF(ISNUMBER(SEARCH("Attorney",U1)),"",ROW(U1:U11019)))

Think you had an error in the above formula,
the last part should be just ROW() as suggested earlier

Try it again, as

Put in AA1: =IF(U1="","",IF(ISNUMBER(SEARCH("Attorney",U1)),"",ROW()))
Copy AA1 down until the last row of data

Note that the formula above goes into col AA, and is then read by the 2nd
formula in Sheet2. If you have changed it to go to into another col other
than col AA, then you'd need to amend the 2nd formula in Sheet2 to point to
the new col.

... if you still can't get it, email a zipped copy of your file over, and
I'll take a look at it this evening ..
 
Top