In I1:
=IF(AND(LEFT(A1)="A",RIGHT(A1)="S"),ROW(),"")

In J1:
=IF(ROW()COUNT(I:I),"",INDEX(A:A,SMALL(I:I,ROW()) ))

In K1:
=IF(J2="","",OFFSET($H$1,MATCH(J2,A:A,0)-2,))
Select I1:K1, copy down to last row of source data. Minimize/Hide away col
I. Cols J & K returns 99.99% desired results. For the last value extracted in
col J, do a manual check & overwrite the blank in col K.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Justin" wrote:
Hello,
I have used the excel discussion group to solve many problems, and now have
one that I need to post!

I have a report that exports data (actually in text format, but that may not
matter) where I need to look down column A and find the cells with "AxxxS"
where the xxx are numbers (well, text) in ascending order. In between the
"AxxxS" values are cells in column a with various meaningless text data.
What I need to find is the maximum value (remember these numbers are exported
to this file as text) in column H that is some rows down from the "AxxxS"
row. The number of rows down can vary, but it always will be the cell with
one row above the next "AxxxS" value.

There are over 1000 rows, so I have to pull around 100 values of "AxxxS" and
the corresponding number in column H. Also, the number of rows between each
"AxxxS" can be as little as two rows, or 15 rows. Lastly, there are other
values in column H, but the one I need is the maximum of those values before
the next "AxxxS" sequence.

I have been trying sumproduct and offset and other combinations, but can't
find a solution, hopefully someone can help!

Here is what the data looks like.

A B --- H
1 A500S
2 othertext
3 othertext
4 othertext 3838
5 A501S
6 othertext
7 othertext 2500
8 A502S
9 othertext
10 othertext
11 othertext 3000
12 A503S
13 othertext
14 othertext
15 othertext
16 othertext 1000
17 A504S
18 othertext
19 othertext
20 othertext 1200


Want to return in columns J and K:

A500S 3838
A501S 2500
A502S 3000
A503S 1000
A504S 1200