I have about 5000 rows of data. I want to complete REMOVE a row of data if the text in the A column matches certain text. Not the entire text, just a substring.

any ideas?

12/15/2008 10:41:42 AM

sounds like its macro time

12/15/2008 10:48:14 AM

In Column B (or a blank column), use IF and FIND to find the substring, and return, for example, a "yes" if it's found and "no" if it's not.

Then, i assume you want to delete the entire row if it is a "yes"?
I have an autofilter trick I use to delete certain rows, but keep the other rows in the same order as before
1) In Column C, or another blank column, number all the rows 1, 2, 3, etc all the way down. Use "Fill Down" so the values are actually in the cells (i.e. don't use a formula)
2) Sort by Column B so all the "yes" and "no" rows are together. delete the "yes" rows
3) Sort by Column C, so all the "no" rows are back in the original order.
4) delete columns B and C

12/15/2008 11:04:29 AM

This question is begging to be asked....why do you have an excel doc with 5000 rows of data?

12/15/2008 11:49:36 AM

^ what?

12/15/2008 11:52:33 AM

because he is a professional at something

lots of folks in a variety of industries have spreadsheets that would dwarf 5000 rows

12/15/2008 12:22:41 PM

I'm sure Talage's point was "well if you have that much data, you should be using a database or JMP/SAS, OMG!"

but everyone else is right. 5000 lines is not a big deal to be using in Excel. Certainly, Excel is one of the most overused tools available, but for the most part, it does just fine for people who cannot or do not want to deal with more complicated software

12/15/2008 12:32:44 PM

Yea the engineers at my company get a copy of all the tooling available in our tool room and it's around 20,000 lines. The original is handeled by some database software but they give it to us in excel so we can find stuff without having to be trained in whatever software it is kept in.

This thread is relevant to my interests.

[Edited on December 15, 2008 at 12:38 PM. Reason : ]

12/15/2008 12:36:27 PM

haha oh noes 5000 rows of data!


B1 is =IF(FIND("RATE1",A1), "yes", "no")

this works if TRUE, but if FALSE, it displays #VALUE! error ... shouldn't it just display "no" ?

[Edited on December 15, 2008 at 12:37 PM. Reason : ]

12/15/2008 12:36:40 PM

Quote :

"This question is begging to be asked....why do you have an excel doc with 5000 rows of data"

I have a few sheets with 157,000+ rows of data. Ohhhh, go me! And I constantly max out Excel's 32,000 data point limit for charts

12/15/2008 12:40:45 PM

^^ it's because when FIND doesn't find the string, it returns #VALUE!, and the IF statement doesn't know how to handle that.

Try (notice the "yes" and "no" are switched)
=IF(ISERROR(FIND("RATE1",A1)), "no", "yes")

12/15/2008 12:48:15 PM

one of my happiest days was when I found out Excel 2k7 was removing the 15-bit row limit

12/15/2008 1:00:58 PM

yeah, what is it now - 2 million?
I most often had problems with the 256 column limit. I think that's up to 32k or something now

12/15/2008 1:07:54 PM

it's 1 million

and ive used about 750k+ before, multiple columns and sheets. I think it was a 500MB file

12/15/2008 1:09:53 PM

ok with that much data, there has to be a better way

12/15/2008 2:40:06 PM

sure I could do it in Matlab, but Excel just makes it so easy for me especially for the types of calculations im doing. array equations ftw

12/15/2008 3:11:59 PM

i cant just imagine was a dog it was

12/15/2008 3:41:17 PM

Is it possible to have the x-axis ranges set to the value of a cell? I'm creating probably a couple hundred different charts and need to set the x-axis range on all of them based on certain criteria. The auto range unfortunately doesn't suit my needs.

1/6/2009 1:38:26 PM

get to clickin son

1/6/2009 1:40:51 PM

i dont think it's possible according to google. it is with vba, and joe#'s doesn't do vba

1/6/2009 2:04:45 PM

vba is easy

1/6/2009 2:36:51 PM

it may be, but it's one of those things i simply don't do. like cleaning a toilet.

1/6/2009 3:05:14 PM

In case you get around to working on the VB, I believe this is the method you want:

http://msdn.microsoft.com/en-us/library/aa214530(office.11).aspx

1/6/2009 3:05:34 PM