| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last value in column before blank
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last value in column before blank
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last value in column before blank
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)&RIG HT(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 "craig" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last value in column before blank
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 -- Biff Microsoft Excel MVP "craig" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last value in column before blank
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 "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "craig" wrote in message ... 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 . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last value in column before blank
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. "Max" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last value in column before blank
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 "Ashish Mathur" wrote: 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)&RIG HT(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 "craig" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last value in column before blank
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. -- Max Singapore --- "craig" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last value in column before blank
=OFFSET(INDEX(A:A,MATCH(C2,A:A,0)),MATCH(TRUE,A$1 0: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 -- Biff Microsoft Excel MVP "craig" wrote in message ... 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 "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "craig" wrote in message ... 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 . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last value in column before blank
Thanks, great explaination. Completely Understood. Thats very cool nesting
the Index = "" to match the value True. Thanks again. "T. Valko" wrote: =OFFSET(INDEX(A:A,MATCH(C2,A:A,0)),MATCH(TRUE,A$1 0: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 -- Biff Microsoft Excel MVP "craig" wrote in message ... 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 "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "craig" wrote in message ... 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 . . |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last value in column before blank
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "craig" wrote in message ... Thanks, great explaination. Completely Understood. Thats very cool nesting the Index = "" to match the value True. Thanks again. "T. Valko" wrote: =OFFSET(INDEX(A:A,MATCH(C2,A:A,0)),MATCH(TRUE,A$1 0: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 -- Biff Microsoft Excel MVP "craig" wrote in message ... 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 "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "craig" wrote in message ... 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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to Sum a column if reference column is blank | Excel Worksheet Functions | |||
| insert a blank column between each column in a data file | Excel Discussion (Misc queries) | |||
| SUM values in this column F only if cell is blank in column Q...HE | Excel Discussion (Misc queries) | |||
| How do I count the items in one column if another column is blank | Excel Worksheet Functions | |||
| Warning message if one column contains any text and another column is blank | Excel Worksheet Functions | |||
Linear Mode
