Return the last values above / before a Specific Value

Sam

New member
Joined
Apr 3, 2014
Messages
36
Reaction score
0
Points
0
Excel Version(s)
2007
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
 

Attachments

  • Book5.xlsx
    10.8 KB · Views: 13
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.
 

Attachments

  • Sam.xlsm
    17.5 KB · Views: 11
Last edited:
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
 
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.
 
Thank you for all your time and effort to produce a perfect formula. It works great!

Very much appreciated,
Sam
 
Back
Top