Need help with multi-row filter/sort

[bstevens]

I call this a "multi-row filter/sort" because that's the best name I
can think of. I think it's more of a filter than a sort. I've hacked
at this for quite a while and haven't come up with a solution yet.
Please help if you have a minute. Here's the problem:

I have a spreadsheet with several thousand rows and about 30 columns.

In Column G, a value is present, ranging from 0 to 100.

I need some kind of formula or routine that will scan down Column G
and find any instances where a value >30 (greater than 30) occurs in
Column G three or more times in a row.

The result needs to be a spreadsheet containing ONLY these rows.

For example:

A B

aaa 25
bbb 27
ccc 38 <
ddd 31 <
eee 48 <
fff 12
ggg 58 <
hhh 41 <
iii 11
jjj 59 <
kkk 72 <
lll 93 <
mmm 88 <
nnn 18
ooo 21

Would produce:

A B
ccc 38
ddd 31
eee 48
jjj 59
kkk 72
lll 93
mmm 88

Would be very grateful for an answer. Please reply to the group only.

Thanks,
Ron M.

 

[Ken]

Ron

The following two column approach should work:

In column C put the formula

=IF(AND(B1>30,B2>30,B3>30),1,0)

That will give you a 1 if a row is part of sequence of 3 rows where
the value in column B is greater than 30 unless it is the first of the
series or the last.

In column D put the formula

=IF(OR(AND(C2=0,C3=1),AND(C2=0,C1=1),C2=1),1,0)

That will pick up the first and last of the series with a 1 and keep
the existing internal rows with 1's

Then you can filter by column D, selecting the 1's and you will have
your list. You will need an extra row at the top and bottom of your
list, or you will have to customize your boundary rows. You may be
able to combine the formulas into one column, but you will probably
have to deal with the circularity.

Good luck.


Ken
Norfolk, Va