Excel/Finding Last Row Containing Value
QUESTION: Is there a way to find the last row a certain value occurred without resorting to VBA or arrays? (computer runs out of firepower before the whole list is searched :S)
ANSWER: I would have thought that a proper VBA function would be the least memory intensive solution, but the only Non VBA, non Array solution I can think of would involve 2 formulas to get the data
In my example, my data is in A1:a9, and I've put the first forumula in column C
You will note I've mixed my references to column A - this is so that as the formula is copied down the sheet the range to test will reduce - so column C will ONLY contain the value of column A the last time it is found.
You can then use the match function to get the row that is on
(in this case, I've hard coded the 5, but in reality it would presumably be another cell reference)
The array formula version (for completeness) would be =MAX(IF($A$1:$A$9=5,ROW($A$1:$A$9),0)) and the VBA method I would use would be a custom function
Public Function MaxVal(rar As Range, vl As Variant) As Long
For Each cl In rar
If cl.Value = vl Then MaxVal = cl.Row
---------- FOLLOW-UP ----------
QUESTION: Thanks so much for helping out.
I would agree that VBA would be the better option but unfortunately, my workplace has disabled that option as SOE.
Re: Data in column A
How would we go about if the data goes across 6 columns?
My array method would still work, just with a wider range of data. For a non array, I think we'd need to use TWO helper columns - the first would check if any value in the row matched the value being searched, so
again hard coded 5, but this would be a cell reference I would guess. This would then give you a column that had the values, which you could then use the decreasing countif formula from yesterday with.
Hopefully this makes sense!