Head-Fi.org › Forums › Misc.-Category Forums › Members' Lounge (General Discussion) › Microsoft Excel (2007) Question
New Posts  All Forums:Forum Nav:

Microsoft Excel (2007) Question

post #1 of 14
3/3/10 at 1:00pm
Thread Starter 
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
Reply
post #2 of 14
3/3/10 at 2:48pm
Sort.
That will put them all together and then you can delete all at once.
(resort after delete if you need original order)
.
Reply
post #3 of 14
3/3/10 at 3:31pm
CTRL-H brings up the "find and replace" window.

At the bottom there is a "replace all" button.
Reply
post #4 of 14
3/3/10 at 4:02pm
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.
Reply
post #5 of 14
3/3/10 at 4:35pm
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.
Reply
post #6 of 14
3/3/10 at 4:40pm
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.
Reply
post #7 of 14
3/3/10 at 5:33pm
Are they all in one column? If so, auto filter the data and just select them all and delete them.
Reply
post #8 of 14
3/3/10 at 5:56pm
^ 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.
Reply
post #9 of 14
3/3/10 at 6:20pm
Quote:
Originally Posted by AmanGeorge View Post
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.
Reply
post #10 of 14
3/3/10 at 6:22pm
"Replace All"
Reply
post #11 of 14
3/3/10 at 7:00pm
Use Octave (free Matlab) - it's awesome.
Reply
post #12 of 14
3/3/10 at 10:39pm
Quote:
Originally Posted by intoflatlines View Post
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!
Reply
post #13 of 14
3/4/10 at 7:41pm
Thread Starter 
Quote:
Originally Posted by mr_baseball_08 View Post
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
Reply
post #14 of 14
3/4/10 at 7:42pm
Thread Starter 
Quote:
Originally Posted by AmanGeorge View Post
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
Reply
New Posts  All Forums:Forum Nav:
  Return Home
Head-Fi.org › Forums › Misc.-Category Forums › Members' Lounge (General Discussion) › Microsoft Excel (2007) Question