Multiple line deletion in excel
 

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!
--
Linda

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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"SafariQn" wrote in message
...
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!
--
Linda

Oops, missed out the assumption line:
Source data is assumed in col A , from A1 down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Could you use the DataFilterAutofilter to find those rows with the specific
word then delete those visible rows.

Select the filtered rows and F5SpecialVisible cells onlyOK

Then EditDeleteEntire Row.


Gord Dibben Excel MVP

On Fri, 30 Sep 2005 16:19:02 -0700, "SafariQn"
wrote:

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!

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.

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
-- Linda wrote:
Hi Max and I have no idea how to thank you for your
help - but..... I'm not sure I understand exactly
what you wrote. I know how to use Excel but I
couldn't quite understand what you said to do in the
formula.

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". Are you telling me
to still use conditional formatting? I'm sorry I
didn't understand, can you explain again please.
Would really appreciate it.

What I'm trying to do is delete all the lines that
have this specific word in it.

Thanks again for your help

Linda

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

(as it's assumed source data is in A1:Z10000)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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


"Max" wrote:

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.

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
-- Linda wrote:
Hi Max and I have no idea how to thank you for your
help - but..... I'm not sure I understand exactly
what you wrote. I know how to use Excel but I
couldn't quite understand what you said to do in the
formula.

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". Are you telling me
to still use conditional formatting? I'm sorry I
didn't understand, can you explain again please.
Would really appreciate it.

What I'm trying to do is delete all the lines that
have this specific word in it.

Thanks again for your help

Linda

=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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Gord:
Thank you very much for your help! Your suggestion was PERFECT!!!
--
Linda


"Gord Dibben" wrote:

Could you use the DataFilterAutofilter to find those rows with the specific
word then delete those visible rows.

Select the filtered rows and F5SpecialVisible cells onlyOK

Then EditDeleteEntire Row.


Gord Dibben Excel MVP

On Fri, 30 Sep 2005 16:19:02 -0700, "SafariQn"
wrote:

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!