Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
I have a column C containing the following:
=IF($B37" ",MatchLast($B37,$B$2:$B36,2)," ") I meant this to find the last entry in column B that matches $B37 - and move its corresponding C value in. For instance, it would move "4" into C37. But I'm getting "5" moved in. I entered MatchLast in my Excel 2003 help and it did not find a match. 34 x test1 5 35 x test1 4 36 x test2 6 37 x test1 ???? How should I change this formula? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
Hello, try this in cell C37
=INDIRECT("$C$"&ROW(INDEX($B$2:$B$36,MAX(($B$2:$B$ 36=$C$37)*ROW($B$2:$B $36))-ROW(OFFSET($B$2:$B$36,0,0,1,1))+1))) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
Give this a try...
=IF(COUNTIF($B1:$B37,$B37)1,INDEX(C1:C36,SUMPRODU CT(MAX(ROW(1:36)*($B1:$B36=$B37)))),"") Rick "Howard Brazee" wrote in message ... I have a column C containing the following: =IF($B37" ",MatchLast($B37,$B$2:$B36,2)," ") I meant this to find the last entry in column B that matches $B37 - and move its corresponding C value in. For instance, it would move "4" into C37. But I'm getting "5" moved in. I entered MatchLast in my Excel 2003 help and it did not find a match. 34 x test1 5 35 x test1 4 36 x test2 6 37 x test1 ???? How should I change this formula? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
On Aug 1, 4:17*pm, GTVT06 wrote:
Hello, try this in cell C37 =INDIRECT("$C$"&ROW(INDEX($B$2:$B$36,MAX(($B$2:$B$ 36=$C$37)*ROW($B$2:$B $36))-ROW(OFFSET($B$2:$B$36,0,0,1,1))+1))) Forgot to tell you this has to be entered in as an array using Ctrl +Shift+Enter after you put in the formula. this should encase the formula in { } brackets when entered in correctly. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
On Fri, 01 Aug 2008 14:55:08 -0600, Howard Brazee
wrote: I have a column C containing the following: =IF($B37" ",MatchLast($B37,$B$2:$B36,2)," ") I meant this to find the last entry in column B that matches $B37 - and move its corresponding C value in. For instance, it would move "4" into C37. But I'm getting "5" moved in. I entered MatchLast in my Excel 2003 help and it did not find a match. 34 x test1 5 35 x test1 4 36 x test2 6 37 x test1 ???? How should I change this formula? If you always have at least one match, you may try this formula in cell C37: =IF($B37"",INDEX(C$1:C36,LARGE(ROW(B$2:B$36)*($B3 7=$B$2:$B36),1)),"") Hope this helps / Lars-Åke |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
One mo
=if($b37="","",LOOKUP(2,1/($B$2:$B$36=$B37),$C$2:$C$36)) I like empty strings--not single space characters--to make the cell look empty. Howard Brazee wrote: I have a column C containing the following: =IF($B37" ",MatchLast($B37,$B$2:$B36,2)," ") I meant this to find the last entry in column B that matches $B37 - and move its corresponding C value in. For instance, it would move "4" into C37. But I'm getting "5" moved in. I entered MatchLast in my Excel 2003 help and it did not find a match. 34 x test1 5 35 x test1 4 36 x test2 6 37 x test1 ???? How should I change this formula? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
I just thought I would make a note that I used my
IF(COUNTIF($B1:$B37,$B37)1... test instead of a test similar to this... IF($B37=""... in order to stop the #N/A error that gets generated if the text in B37 was not empty, but also didn't appear in any of the earlier cells of Column B. Rick "Dave Peterson" wrote in message ... One mo =if($b37="","",LOOKUP(2,1/($B$2:$B$36=$B37),$C$2:$C$36)) I like empty strings--not single space characters--to make the cell look empty. Howard Brazee wrote: I have a column C containing the following: =IF($B37" ",MatchLast($B37,$B$2:$B36,2)," ") I meant this to find the last entry in column B that matches $B37 - and move its corresponding C value in. For instance, it would move "4" into C37. But I'm getting "5" moved in. I entered MatchLast in my Excel 2003 help and it did not find a match. 34 x test1 5 35 x test1 4 36 x test2 6 37 x test1 ???? How should I change this formula? -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
On Fri, 1 Aug 2008 14:20:07 -0700 (PDT), GTVT06
wrote: On Aug 1, 4:17*pm, GTVT06 wrote: Hello, try this in cell C37 =INDIRECT("$C$"&ROW(INDEX($B$2:$B$36,MAX(($B$2:$B$ 36=$C$37)*ROW($B$2:$B $36))-ROW(OFFSET($B$2:$B$36,0,0,1,1))+1))) I got a circular reference. Forgot to tell you this has to be entered in as an array using Ctrl +Shift+Enter after you put in the formula. this should encase the formula in { } brackets when entered in correctly. I'm not familiar with this, but I copied the formula to a text editor, moved it to one line, then copied it to cell C37, pasted it, hit Ctl/Shift/Enter and it put brackets around it. But it displayed a zero. Could you explain what this did, so that I can try to debug it? Thanks. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
On Fri, 1 Aug 2008 17:17:39 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: Give this a try... =IF(COUNTIF($B1:$B37,$B37)1,INDEX(C1:C36,SUMPROD UCT(MAX(ROW(1:36)*($B1:$B36=$B37)))),"") Rick That worked for B37, but I neglected to say that this is a spreadsheet that is growing. Every week I add a new line. I'm up to line 50 now. Copying B37 down got bad references. When I get a new value, I overwrite this formula with the new value. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
On Fri, 01 Aug 2008 21:23:42 GMT, Lars-Åke Aspelin
wrote: If you always have at least one match, you may try this formula in cell C37: =IF($B37"",INDEX(C$1:C36,LARGE(ROW(B$2:B$36)*($B 37=$B$2:$B36),1)),"") I put in a new row each week, whenever there is a new value in the B column, I overtype the C formula with its corresponding new value. My first set of values is in row 3, I have the formula currently set to go to row 100, although I only have the first 50 rows populated with data. Your formula displayed #### for me. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
On Fri, 01 Aug 2008 19:52:13 -0500, Dave Peterson
wrote: One mo =if($b37="","",LOOKUP(2,1/($B$2:$B$36=$B37),$C$2:$C$36)) I like empty strings--not single space characters--to make the cell look empty. That appears to work for me, thanks. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
On Mon, 04 Aug 2008 07:43:10 -0600, Howard Brazee
wrote: On Fri, 1 Aug 2008 14:20:07 -0700 (PDT), GTVT06 wrote: On Aug 1, 4:17*pm, GTVT06 wrote: Hello, try this in cell C37 =INDIRECT("$C$"&ROW(INDEX($B$2:$B$36,MAX(($B$2:$B$ 36=$C$37)*ROW($B$2:$B $36))-ROW(OFFSET($B$2:$B$36,0,0,1,1))+1))) I got a circular reference. Forgot to tell you this has to be entered in as an array using Ctrl +Shift+Enter after you put in the formula. this should encase the formula in { } brackets when entered in correctly. I'm not familiar with this, but I copied the formula to a text editor, moved it to one line, then copied it to cell C37, pasted it, hit Ctl/Shift/Enter and it put brackets around it. But it displayed a zero. Could you explain what this did, so that I can try to debug it? Thanks. Try changing the $C$37 to $B$36 in the formula above. (The formula should still be in cell C37) Lars-Åke |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
On Mon, 04 Aug 2008 14:22:03 GMT, Lars-Åke Aspelin
wrote: On Mon, 04 Aug 2008 07:43:10 -0600, Howard Brazee wrote: On Fri, 1 Aug 2008 14:20:07 -0700 (PDT), GTVT06 wrote: On Aug 1, 4:17*pm, GTVT06 wrote: Hello, try this in cell C37 =INDIRECT("$C$"&ROW(INDEX($B$2:$B$36,MAX(($B$2:$B$ 36=$C$37)*ROW($B$2:$B $36))-ROW(OFFSET($B$2:$B$36,0,0,1,1))+1))) I got a circular reference. Forgot to tell you this has to be entered in as an array using Ctrl +Shift+Enter after you put in the formula. this should encase the formula in { } brackets when entered in correctly. I'm not familiar with this, but I copied the formula to a text editor, moved it to one line, then copied it to cell C37, pasted it, hit Ctl/Shift/Enter and it put brackets around it. But it displayed a zero. Could you explain what this did, so that I can try to debug it? Thanks. Try changing the $C$37 to $B$36 in the formula above. (The formula should still be in cell C37) Lars-Åke Sorry, I meant, "change $C$37 to $B$37" |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
On Mon, 04 Aug 2008 07:54:19 -0600, Howard Brazee
wrote: One mo =if($b37="","",LOOKUP(2,1/($B$2:$B$36=$B37),$C$2:$C$36)) I like empty strings--not single space characters--to make the cell look empty. That appears to work for me, thanks. Nope, it worked plugging some in. Then I copied it to cells C3 through C100 and filled in the values by hand for C3, C4, C8, C27, C35, & C43 (which correspond to new B3, B4, B8, B27, B35, & B43 values), and it didn't work. So I changed cell C37 to be: =if($b37="","",LOOKUP(2,1/($B$3:$B36=$B37),$C$3:$C36)) and then copied it up and down from C5-C100 (keeping my overwritten C3 & C4). The other new values showed ##N/A until I populated them (overwriting the formula with the values). The correct figures are populating, thanks. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
MatchLast function
On Mon, 04 Aug 2008 07:51:36 -0600, Howard Brazee
wrote: On Fri, 01 Aug 2008 21:23:42 GMT, Lars-Åke Aspelin wrote: If you always have at least one match, you may try this formula in cell C37: =IF($B37"",INDEX(C$1:C36,LARGE(ROW(B$2:B$36)*($ B37=$B$2:$B36),1)),"") I put in a new row each week, whenever there is a new value in the B column, I overtype the C formula with its corresponding new value. My first set of values is in row 3, I have the formula currently set to go to row 100, although I only have the first 50 rows populated with data. Your formula displayed #### for me. I forgot to mention that this formula has to be entered as an array formula, i.e. with CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? | Excel Programming | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions |