Deleting rows from a worksheet automatically
 

Hi,

I have a spreadsheet that looks something like this

A B C
D E F

5222875270 8 HNLSFOORDMAN G7849NA UK XGA
5222897579 8 HNLSFODENMAN G7899NA UK XGB
5222897581 8 HNLSFOORDMAN G7899NA UK XGB
5256311073 9 IADJFKEWRCHIMAN H5825CS UK XGA
5256311075 9 IADJFKEWRORDMAN H5825CS UK XGA
5256311077 9 IADJFKEWRJFKMAN H5825CS UK XGA
5253439823 7 IADORDMAN Q5927TX UK XGB
5253439825 7 IADORDMAN Q5927TX UK XGB

Only mine is about 40,000 rows! Now I want to strip out of that worksheet
any rows where column C does not contain the letters ORD anywhere in the
string. Is there a way of doing this without manually going through it? I
just need the rows deleted, they do not need keeping for any purpose.

Thanks in advance for any help.

John

Deleting rows from a worksheet automatically
 

One approach would be to use in unused column with the formula:
=IF(ISERROR(FIND("ord",C2)),"Not Found","")

as this will evaulate the string C2, if it finds "ORD" anywhere, the result
will be null "", if it doesn't find it the result will be "Not Found". Next,
sort the data by this temporary column and then delete all the rows with
"Not Found" listed in that column.


"mg_sv_r" wrote in message
...
Hi,

I have a spreadsheet that looks something like this

A B C
D E F

5222875270 8 HNLSFOORDMAN G7849NA UK XGA
5222897579 8 HNLSFODENMAN G7899NA UK XGB
5222897581 8 HNLSFOORDMAN G7899NA UK XGB
5256311073 9 IADJFKEWRCHIMAN H5825CS UK XGA
5256311075 9 IADJFKEWRORDMAN H5825CS UK XGA
5256311077 9 IADJFKEWRJFKMAN H5825CS UK XGA
5253439823 7 IADORDMAN Q5927TX UK XGB
5253439825 7 IADORDMAN Q5927TX UK XGB

Only mine is about 40,000 rows! Now I want to strip out of that worksheet
any rows where column C does not contain the letters ORD anywhere in the
string. Is there a way of doing this without manually going through it? I
just need the rows deleted, they do not need keeping for any purpose.

Thanks in advance for any help.

John

Deleting rows from a worksheet automatically
 

You may use a formula like this in an additional column (G, for example)
=ISNUMBER(FIND("ORD",C1))
Then copy the formula all along the range, sort on that column and delete
all the rows that have FALSE on it.

Hope this helps,
Miguel.

"mg_sv_r" wrote:

Hi,

I have a spreadsheet that looks something like this

A B C
D E F

5222875270 8 HNLSFOORDMAN G7849NA UK XGA
5222897579 8 HNLSFODENMAN G7899NA UK XGB
5222897581 8 HNLSFOORDMAN G7899NA UK XGB
5256311073 9 IADJFKEWRCHIMAN H5825CS UK XGA
5256311075 9 IADJFKEWRORDMAN H5825CS UK XGA
5256311077 9 IADJFKEWRJFKMAN H5825CS UK XGA
5253439823 7 IADORDMAN Q5927TX UK XGB
5253439825 7 IADORDMAN Q5927TX UK XGB

Only mine is about 40,000 rows! Now I want to strip out of that worksheet
any rows where column C does not contain the letters ORD anywhere in the
string. Is there a way of doing this without manually going through it? I
just need the rows deleted, they do not need keeping for any purpose.

Thanks in advance for any help.

John