One formulas play ...
Assume your source data running in A2 down
Put these in B2:D2
In B2: =IF(LEFT(A2)="V",A2,IF(A2="",A1,""))
In C2: =IF(OR(B2={0,""}),"",ROW())
In D2: =INDEX(B:B,SMALL(C:C,ROWS($1:1)))
Copy down to last row of source data. Col D returns the "product-last date"
data pairs in alternating fashion, which can then easily be extracted via
In E2: =OFFSET($D$2,ROWS($1:1)*2-2+COLUMNS($A:A)-1,)
Copy E2 to F2, fill down to exhaust. Format col F as dates to taste. voila?
immortalize it, hit YES below
--
Max
Singapore
---
"craig" wrote:
Hi, My data in one column A:A - Where V100 &V101 are product codes, dates run
down the column and Blank is a blank cell befor each new product code.
V100
1-Sep-09
2-Sep-09
Blank
V101
1-Oct-09
5-Oct-09
10-Oct-09
Blank

Question is how do i return the date value for the last entry (eg before the
blank) for each product code I want to find. Note also the last date entry is
always the max value date. Eg V100 = 2-Sep and V101 =10-Oct.
Thks Craig