[Nick 214]

Hey guys,

I'm doing a graphing project on Excel using data: records of sea water temperature +/- 50* on the equator with roughly 4,000 points, separated into about 20 graphs. Here's the problem- there are many erroneous points where the instrument failed, and are represented by "-9.99." I need to remove these to have a correct graph.

I was finding them via CRTL+F, but is there a way to delete them all (hundreds!) is one fell?

Thanks a million (or 4,000),

NK

 

[Justin Uthadude]

Sort.
That will put them all together and then you can delete all at once.
(resort after delete if you need original order)
.

 

[acidbasement]

CTRL-H brings up the "find and replace" window.

At the bottom there is a "replace all" button.

 

[intoflatlines]

I would just click in the column you are looking at go to the "data" tab and hit "sort" a-z or z-a, awhich which will sort the rows in order. Then you can delete those entire rows.

 

[AmanGeorge]

Don't do what intoflatlines said if you have multiple columns of data. Highlight your entire data set, then on the Home tab click Sort and Filter, Custom Sort, and sort by the column in question. Then delete all the rows for which that value is -9.99.

 

[mr_baseball_08]

If none of these solutions work, PM me and I can write a VBA macro to search for all instances of -9.99 and rewrite them with whatever you want, or delete them altogether.

 

[pat1006]

Are they all in one column? If so, auto filter the data and just select them all and delete them.

 

[tintin47]

^ usually not. I have to work with a lot of downloaded weather data sets like this, and the distribution is pretty random. I use a combination of many of the strategies mentioned above based on the context.

 

[intoflatlines]

Quote:

Originally Posted by AmanGeorge /img/forum/go_quote.gif Don't do what intoflatlines said if you have multiple columns of data.

If you're implying that the method I use only sorts the values in that column and keeps the other columns the same (messing up the rows/groups of data), that is not true. I do it all the time, and it keeps the corresponding values in each column grouped together. In fact, I just tried it on some data I have here and it worked perfectly.

 

[koven]

"Replace All"

 

[DanielCox]

Use Octave (free Matlab) - it's awesome.

 

[AmanGeorge]

Quote:

Originally Posted by intoflatlines /img/forum/go_quote.gif If you're implying that the method I use only sorts the values in that column and keeps the other columns the same (messing up the rows/groups of data), that is not true. I do it all the time, and it keeps the corresponding values in each column grouped together. In fact, I just tried it on some data I have here and it worked perfectly.

Oh, touche. I just tried it in Excel 2007 and it worked just as you said - apparently Microsoft is smarter than I am. My b!

 

[Nick 214]

Quote:

Originally Posted by mr_baseball_08 /img/forum/go_quote.gif If none of these solutions work, PM me and I can write a VBA macro to search for all instances of -9.99 and rewrite them with whatever you want, or delete them altogether.

Wow! That's a kind offer; it worked out, but thanks!

NK

 

[Nick 214]

Quote:

Originally Posted by AmanGeorge /img/forum/go_quote.gif Don't do what intoflatlines said if you have multiple columns of data. Highlight your entire data set, then on the Home tab click Sort and Filter, Custom Sort, and sort by the column in question. Then delete all the rows for which that value is -9.99.

It worked, thanks! I knew the "replace" bit (not my first rodeo) the problem was finding a variable to replace it with.

Thanks guys!!

NK