Results 1 to 6 of 6

Thread: Return the last values above / before a Specific Value

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

    Return the last values above / before 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 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 above/before 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 3 values above/before 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; input cell for quantity of values to return; error trap if fewer values available to be returned than requested; return the values across a single row.

    Sample Layout:
    Book5.xlsx attached


    Expected Results:
    Looking for last criterion of 0 (zero) in table –
    Found in column “V”, row 14; return 1 value above/before criterion = 7

    Looking for last criterion of 7 in table –
    Found in column “V”, row 13; return 6 values above/before criterion = 1

    Looking for last criterion of 14 in table –
    Found in column “P”, row 18; return 3 values above/before criterion = nothing, empty text(“”)

    Hope you can help.

    Thanks,
    Sam
    Attached Files Attached Files

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

    I added a UDF (in order to be able to concatenate multiple results into one cell).

    In the VB Editor I added following UDF:

    Code:
    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function
    I added another sheet for summary.

    In B1 enter the lookup value (the number you want to find the last row of)
    in B2 enter the number of returned results you want
    in B3 is helper formula to find last row number for the lookup value: =MAX(INDEX((Data=B1)*(ROW(Data)-MIN(ROW(Data))+1),0))-1
    in B4 is helper formula to find column where the last row for the lookup value was found. =MATCH(LOOKUP(2,1/(INDEX(Data,MAX(INDEX((Data=B1)*(ROW(Data)-MIN(ROW(Data))+1),0)),0)=B1),INDEX(Data,1,0)),INDEX(Data,1,0),0)
    in B5 is formula:

    =IFERROR(aconcat(SMALL(IF(INDEX(Data,2,B4):INDEX(Data,B3,B4)<>"",INDEX(Data,2,B4):INDEX(Data,B3,B4)),ROW(INDIRECT("1:"&MIN(B2,COUNTIF(INDEX(Data,2,B4):INDEX(Data,B3,B4),">=0"))))),","),"")

    confirmed with CTRL+SHIFT+ENTER not just ENTER.
    Attached Files Attached Files
    Last edited by NBVC; 2014-04-28 at 04:54 PM.


  3. #3
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    Fantastic! I really appreciate you taking the time to provide me with such a well thought through solution. Brilliant!

    Is it possible for you to provide me with a version of the solution that returns the results to separate, individual cells across a row (without using the UDF aconcat).


    Thank you,
    Sam

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    In B5 of Sheet1

    =IFERROR(INDEX(INDEX(Data,2,$B4):INDEX(Data,$B3,$B4),SMALL(IF(INDEX(Data,2,$B4):INDEX(Data,$B3,$B4)<>"",ROW(INDEX(Data,2,$B4):INDEX(Data,$B3,$B4))-MIN(ROW(INDEX(Data,2,$B4):INDEX(Data,$B3,$B4)))+1),COLUMNS($B$5:B$5))),"")

    confirmed with CTRL+SHIFT+ENTER not just ENTER and copied across an equal amount of cell as max you would enter in B2 for number of returns requested.


  5. #5
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    Thank you for all your time and effort to produce a perfect formula. It works great!

    Very much appreciated,
    Sam

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    You're welcome. Thanks for the feedback.


Posting Permissions

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