Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Return the last values below / after a Specific Value

  1. #1
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007

    Return the last values below / after a Specific Value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I am using Excel 2007 for Windows.

    I have had assistance with a similar scenario, and have tried to adjust the formula but I am not getting the results I expect.

    I have a table that consists of many columns and expanding rows; for this reason, I have referenced the table as a dynamic named range called “Data”. “Data” can contain duplicate values in both columns and rows. I have numeric labels in row 3, spanning the width of my table; “Data” starts in column “G”, row 4. Column “A” contains sequential numeric values (references) that can be used as relative row numbers for my table. The cells within the table are populated with a formula that returns either a numeric value or empty text (“”) showing a blank cell. Zero is a valid numeric value in the table.

    Scenario:
    Find the last instance of a specific numeric value (criterion) within “Data”.
    From that instance, I would then like to return between 1 and more of the values located below/after the last instance of the specified criterion.

    1. Find the last instance of a specific criterion within “Data”.
    2. Based on last instance of the specific criterion, return the last value(s) below/after that last instance. The number of values to return will vary, and there may be no values to return.

    3. Using dynamic name “Data” in a flexible formula based solution with an input cell for the criterion; error trap if no values available to be returned; return the values to separate cells across a single row.

    Sample Layout of table "Data":
    Book5a.xlsx attached


    Expected Results:
    Looking for last criterion of 0 (zero) in table –
    Found in column “V”, row 14; return value(s) below/after criterion = 2

    Looking for last criterion of 4 in table –
    Found in column “AA”, row 11; return value(s) below/after criterion = 5

    Looking for last criterion of 7 in table –
    Found in column “V”, row 13; return value(s) below/after criterion = 0, 2

    Looking for last criterion of 14 in table –
    Found in column “P”, row 18; return value(s) below/after criterion = nothing, empty text(“”)

    Hope you can help.

    Thanks,
    Sam
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    In the attached there are formulae like:
    =LastValues($H2,Data,I$1,TRUE)
    where:
    =LastValues(ValueSought,DataRange,AboveOrBelow,OneCellResult)

    I've included stuff like AboveOrBelow (you supply a string being "Above" or "Below", or a reference to a cell containing that string) because of your comment in cell A10 of Sheet1,
    and stuff like OneCellResult because you have both kinds of result in your sheet (here you supply TRUE or FALSE or a reference to a cell with TRUE or FALSE).
    I can remove them if you want.

    I supplied a udf rather than a formula because (a) you already had a macro/function and (b) it's a lot easier to enter and maintain than a megaformula.

    Note that a line in the udf is:
    Set rngData = DataRange.Offset(1).Resize(DataRange.Rows.Count - 1)
    which is there to lop the top row off your named range Data.

    I'm sure I could make the code slicker, only I've run out of time.
    Attached Files Attached Files

  3. #3
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    I really do appreciate you taking the time to provide me with this great udf solution. It really is brilliant! Thank you.


    [QUOTE=I supplied a udf rather than a formula because (a) you already had a macro/function and (b) it's a lot easier to enter and maintain than a megaformula..[/QUOTE]

    If possible would still appreciate it if you could provide a formula.


    Thank you,
    Sam

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    Well, I've been trying but failing.
    Perhaps others may know of a way.

    In the attached version of the previous file I posted I've tried developing a formula and got to cell AK20 (working on the same sheet as the table). This uses a helper column AJ.
    I can't do that final step of incorporating the formula in column AJ into the final mega-formula.

    I wouldn't like to maintain a megaformula such as this.
    Attached Files Attached Files

  5. #5
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    Thank you ever so much for all your help, and for taking the time to attempt the mega-formula.

    Hope someone can help.

    Sam

  6. #6
    See if this array formula does it for you

    =ADDRESS(MAX(IF(Data=$A$1,ROW(Data))),MAX(IF(INDEX(Data,MAX(IF(Data=$A$1,ROW(Data)))-ROW(Data)+1,0)=$A$1,COLUMN(Data))),4)

    where Data is the grid, A3:AD18, and $A$1 holds the value to lookup
    Last edited by Bob Phillips; 2014-05-07 at 09:54 AM.

  7. #7
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    Hi Bob,

    Quote Originally Posted by Bob Phillips View Post
    See if this array formula does it for you

    =ADDRESS(MAX(IF(Data=$A$1,ROW(Data))),MAX(IF(INDEX(Data,MAX(IF(Data=$A$1,ROW(Data)))-ROW(Data)+1,0)=$A$1,COLUMN(Data))),4)

    where Data is the grid, A3:AD18, and $A$1 holds the value to lookup
    Thank you for assistance, but this array formula returns the cell address of the last instance of the lookup value, and not the value below / after it which is what I need. Your help is appreciated.

    Sam

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Assuming you enter the column number of interest in say AG2 (i.e. 16 for "V") and the value to find in AH2 (i.e. 7), then try:

    =IFERROR(INDEX(INDEX($G$4:$AD$18,0,$AG$2),SMALL(IF(INDEX($G$4:$AD$18,0,$AG$2)<>"",IF(ROW($G$4:$AD$18)-ROW($G$4)+1>LARGE(IF(INDEX($G$4:$AD$18,0,$AG$2)=$AH$2,ROW($G$4:$AD$18)-ROW($G$4)+1),1),ROW($G$4:$AD$18)-ROW($G$4)+1)),ROWS($A$1:$A1))),"")

    confirmed with CTRL+SHIFT+ENTER, not just ENTER

    and copied down 15 cells (to match table height).


  9. #9
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    Thank you for assistance. Unfortunately, when I apply the formula (using CTRL+SHIFT+ENTER), I get empty text ("") returned as the results.

    Quote Originally Posted by NBVC View Post
    Assuming you enter the column number of interest in say AG2 (i.e. 16 for "V") and the value to find in AH2 (i.e. 7), then try:

    =IFERROR(INDEX(INDEX($G$4:$AD$18,0,$AG$2),SMALL(IF(INDEX($G$4:$AD$18,0,$AG$2)<>"",IF(ROW($G$4:$AD$18)-ROW($G$4)+1>LARGE(IF(INDEX($G$4:$AD$18,0,$AG$2)=$AH$2,ROW($G$4:$AD$18)-ROW($G$4)+1),1),ROW($G$4:$AD$18)-ROW($G$4)+1)),ROWS($A$1:$A1))),"")

    confirmed with CTRL+SHIFT+ENTER, not just ENTER

    and copied down 15 cells (to match table height).
    Can I just confirm that your cell AH2 holds the last instance of the value to find, and that the formula should then return the value that is located below /after that specific last instance of the value in AH2. Any further help most appreciated.

    Sam

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    See attached.

    AH2 holds the value that you want to find in order to get the values below it in the select column (entered in AG2)
    Attached Files Attached Files


Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •