Last value in column before blank

[craig]

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

 

[Max]

One formulas play ...
Assume your source data running in A2 down
Put these in B22
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

 

[Ashish Mathur]

Hi,

Assume data in in range C4:C18. The inherent assumption in this formula is
that the last digit of the product code increments by 1. Try this

=MAX(INDEX($C$4:$C$18,MATCH(F20,$C$4:$C$18,0)+1,1):INDEX($C$4:$C$18,IF(ISERROR(MATCH(LEFT(F20,3)&RIGHT(F20,1)+1,$C$4:$C$18,0)-1),ROWS($C$4:$C$18),MATCH(LEFT(F20,3)&RIGHT(F20,1)+1,$C$4:$C$18,0)-1),1))

F20 has is the user entry cell for the the product code I.e. V100, V101 etc.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

Try this array formula**.

There *must* be at least one empty cell at the end of the range of your
data.

Assuming your data is in the range A2:A10 (A10 being the empty cell after
the last date entry for V101).

C2 = product code to lookup

Array entered** in D2:

=OFFSET(INDEX(A:A,MATCH(C2,A:A,0)),MATCH(TRUE,A$10:INDEX(A:A,MATCH(C2,A:A,0))="",0)-2,0)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date

 

[craig]

Biff, You are the Man! Perfect.

Just one last question just to help me understand the formula.. I'm across
the index, match and offset formulas generally.... but cant quite understand
the Match formula to find the rows in the offset, specifically why is the
lookup value "TRUE" and why does the lookup array start from the bottom of
range. Cant quite grasp the whole nesting in that match formula.

Appreciate your help

 

[craig]

Max, I really appreciate the reply...but no voila I'm affraid. I dont want to
have to run formuals off the side of my source data (generally ever)
considering its 120k rows. T.Valko's answer was the voila. But Thanks very
much for the reply.

 

[craig]

Thanks Ashish...but my product code didnt increment by one. I know I could
modify your suggestion but T.Valkos's formula was a little cleaner to use.
Thanks Heaps for your reply

 

[Max]

Well, that had a markedly different angle to it, where you get all of the
results dredged out into 2 side-by-side cols in one swift go. Add another 10
secs to kill all the formulas and clean up, and you're there. You neither
have to input nor recalc again, just use autofilter on the 2 results col to
retrieve it on demand.

 

[T. Valko]

=OFFSET(INDEX(A:A,MATCH(C2,A:A,0)),MATCH(TRUE,A$10:INDEX(A:A,MATCH(C2,A:A,0))="",0)-2,0)

Here's how it works...

A2...V100
A3...1-Sep-09
A4...2-Sep-09
A5
A6...V101
A7...1-Oct-09
A8...5-Oct-09
A9...10-Oct-09
A10

Let's assume the lookup value is V101 in C2.

=OFFSET(INDEX(A:A,MATCH(C2,A:A,0))...

The INDEX/MATCH finds V101 in the range and returns that cell address to
OFFSET:

=OFFSET($A$6...

This defines the start of the range for the product code V101.

The information we want is located immediately above the first empty cell
for each product code. So, we need to find the first empty cell in the range
that is below the product code we're looking for. We do that with:

MATCH(TRUE,A$10:INDEX(A:A,MATCH(C3,A:A,0))="",0)

Once again we use INDEX/MATCH to find V101 in the range and that defines the
starting point for the MATCH lookup_array.

A$10:INDEX(A:A,MATCH(C3,A:A,0))

A$10:$A$6

Writing that expression as I did starting with A$10 is just my personal
preference. You can also write it like this:

INDEX(A:A,MATCH(C3,A:A,0)):A$10

Either way, Excel will evaluate it as $A$6:A$10.

Now we have the range that relates to product code V101 - A6:A10. So, we
need to find the first empty cell in that range. We do that with this:

MATCH(TRUE,$A$6:A$10="",0)

The expression:

$A$6:A$10="" will return an array of either TRUE or FALSE

If the cell is blank(empty) = TRUE, if the cell is not blank(empty) = FALSE

A6 = "" = FALSE
A7 = "" = FALSE
A8 = "" = FALSE
A9 = "" = FALSE
A10 = "" = TRUE

MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;TRUE},0)

= 5

At this point we have:

=OFFSET($A$6,5...

We need to make an offset correction which is 2 (rows) so:

=OFFSET($A$6,5-2,0)

=OFFSET($A$6,3,0)

To find the result we're looking for:

OFFSET A6 by 3 rows and 0 columns = A9

A9 = 10/10/2009

So:

=OFFSET(INDEX(A:A,MATCH(C2,A:A,0)),MATCH(TRUE,A$10:INDEX(A:A,MATCH(C2,A:A,0))="",0)-2,0)

= 10/10/2009



exp101

 

[craig]

Thanks, great explaination. Completely Understood. Thats very cool nesting
the Index = "" to match the value True. Thanks again.

 

[T. Valko]

You're welcome. Thanks for the feedback!