Filter but not quite

SamS · Feb 28th, 2002, 10:18 PM

I have some 50,000 lines of imported data and the last column shows either "OK" or "CHECK". If it is "CHECK" I need to view both this row and the preceding row to review the data.

As there are some 300 such rows I have to review is there a way to hide all other rows other than the "CHECK" and the preceding rows I require.

Ivan F Moala · Feb 28th, 2002, 10:45 PM

Hi

You could create another column and place
this formula in the column

Assuming Data starts @ B5
=IF(B6="CHECK","CHECK",B5)

Then fill down

Now you can Autofilter on this column
and select CHECK.

All the formula is doing is placeing a CHECK
in the row above the CHECK row so that autofilter will do this row as well.

HTH

Ivan

SamS · Feb 28th, 2002, 10:55 PM

Of course - I was thinking of a more complex solution - thanks again

Mark W. · Mar 1st, 2002, 01:34 PM

This could be accomplished without adding 50,000 formulas to your worksheet...

Use an Advanced AutoFilter with a computed criteria. Suppose your "CHECK"/"OK" values are in column B beginning on row 2. You could enter the formula, =OR(B2="CHECK",B3="CHECK"), into cell E2 and declare your Advanced AutoFilter Criteria range as E1:E2.

[ This Message was edited by: Mark W. on 2002-03-01 12:35 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 28th, 2002, 10:18 PM	#1
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	I have some 50,000 lines of imported data and the last column shows either "OK" or "CHECK". If it is "CHECK" I need to view both this row and the preceding row to review the data. As there are some 300 such rows I have to review is there a way to hide all other rows other than the "CHECK" and the preceding rows I require.

Feb 28th, 2002, 10:45 PM	#2
Ivan F Moala MrExcel MVP Join Date: Feb 2002 Location: Auckland, New Zealand Posts: 4,209	Hi You could create another column and place this formula in the column Assuming Data starts @ B5 =IF(B6="CHECK","CHECK",B5) Then fill down Now you can Autofilter on this column and select CHECK. All the formula is doing is placeing a CHECK in the row above the CHECK row so that autofilter will do this row as well. HTH Ivan

Feb 28th, 2002, 10:55 PM	#3
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	Of course - I was thinking of a more complex solution - thanks again

Mar 1st, 2002, 01:34 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	This could be accomplished without adding 50,000 formulas to your worksheet... Use an Advanced AutoFilter with a computed criteria. Suppose your "CHECK"/"OK" values are in column B beginning on row 2. You could enter the formula, =OR(B2="CHECK",B3="CHECK"), into cell E2 and declare your Advanced AutoFilter Criteria range as E1:E2. [ This Message was edited by: Mark W. on 2002-03-01 12:35 ]