Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet protection with grouped rows? | Excel Worksheet Functions | |||
Find duplicate rows then deleting them | Excel Worksheet Functions | |||
How do I reduce number of Rows displayed in Excel worksheet? | Setting up and Configuration of Excel | |||
How do I add rows to a completed worksheet? | Excel Worksheet Functions | |||
Display selected rows from one worksheet to another | Excel Worksheet Functions |