Conditional Find & Replace in Excel 2003 or 2007

[bassoprofundo]

Hey all,

I have a massive Excel spreadsheet and need to do a find and replace as in the following example.

Within a given row, If Column K value equals "R", set the value for Column L to "1"

I want to do this for every such instance in the entire sheet in one batch rather than manually hunting each one down. I know how to do a "mass" replace on all of a certain value within the whole sheet, or within a given row and column, but in this instance, I only want to do the replace the value for Column L if the Column K value meets the criteria specified. Help! I don't want to have to comb thousands of lines... Thanks in advance!

 

[mayest]

I don't know if this will solve your problem, but it is interesting. If you apply an AutoFilter (Data --> Filter --> AutoFilter) then Find and Replace is restricted to only the displayed results. So, apply an AutoFilter and then filter on column K to show only the R's. Then do your Find and Replace.

The thing that confuses me is that you said you want to set the value of Column L to 1 whenever Column K = R. Shouldn't you just use an If statement in Column L?

 

[bassoprofundo]

I think the AutoFilter should do the trick. Like you said, it'll limit the find & replace search results then. Thanks!

P.S.- I would use the IF statement in L, but I can't modify the overall structure of the document. It's a company-standard template, and I can't add any field dependencies, formulas, macros, etc. I'm only allowed to modify the field values. Grrr...

 

[bassoprofundo]

AutoFilter did it. Easy enough. Thanks again!