I had an application wherein I had to create a function or formula to get the last filled value in range for an excel spreadsheet.
As seen in the above snap, the cells highlighted in red are updated by user. Currently it has values such as A1 to A8 but the fields could be blank or even filled with anything or some formulas as well. The objective here was irrespective of the value the formula should only return the value based on its positions. i.e. here the last filled value was A8 which in the 8th column which is the last value.
Lets build the formula together, its not just excel formulas that we need here but we need knowledge of a few mathematical concepts here as well.
First
We need to find a way to get the position of the objects that are filled. So here a way is to check if things are blank or not. You can just create a matrix of the cells and put the operate the function on the cells selected.
This returns if cells contain values or they are blank. For the case that is shown in the snap above debugging the above values return Boolean response. True if there is something in the cell and false if the cells contain nothing.
You see here we have the values as False for each cell. But this false converts to "0".
Second
Now that we have a way to find if something is filled up or not not its time to find its position.
The solution as evident from the above snap was to use the column function. This just yields the positions of the columns. All columns in excel have a position number that can be used in formulas containing such position based references.
Here you can see that column number are shown as array in the formula bar.
We now know if a cell is filled or not and we also know the position of the cell in the array, the only thing that remains to find last value that is filled in the array. The simple solution is to perform a matrix multiplication. But if you remember we used ISBLANK which returns false when there is a value, just imagine if you multiply numbers with "0". You'll get the entire array as "0 0 0 0" which is of no use. So it's time to use the not gate I mean the not function which converts 0 to 1 and 1 to 0 .
Finally you get an array that's same as the one shown in the snap above. For better illustration, I'm deleting a few values and from the original dataset.
The array ISBLANK multiplied by COLUMN now returns the following array.
You can see that 3rd, 5th, 7th and 8th Values are turned to 0.
In this entire array 11 is the largest number which is the position of the last filled cell containing "A6".
Final
Now that you have both the values just find the max and put the entire function and put it in an index match formula to index A6 back in the cell below "Last Filled Value"
This is pretty much easy going forward. After thinking about solutions coming out, Before this solution I had ended up creating a custom formula using VBA which was as follow:
This is based on a program that loops through the range and returns the last value filled in the range.
Function LastValue(Rangestart As Variant) As String
Dim i As Byte
Dim lasttext As String
Dim TempHold As String
For i = 1 To 8
TempHold = ActiveSheet.Range(Rangestart.Address).Cells(1, i).Value
If TempHold <> "" Then
lasttext = TempHold
End If
Next i
LastValue = lasttext
End Function
The problem with custom formulas is that they often end up breaking or not working efficiently as the native formulas from excel work.
Hope this was useful, feel free to comment with your methods to solve this task.
Thanks for reading.