Excel: Last Entry28 Aug 2009
I was challenged to work out a little problem someone was having with Excel today. The expectation was that I could write some VBA to resolve it, but once I found out what the task was I quickly managed to put together a purely Excel function based solution.
The task was to pick the last entry from a vertical list of cells. The list is sequential so there are no blank lines which makes the choice of algorithm to find the last entry quite open. The algorithm I chose was based on a list length and vector approach.
So to start with I needed to know how many entries were in the list. The
COUNTA function will provide this. This in effect tells us how many
rows we have to step down to get to the last entry. The
function allows us to carry out these steps based on the first entry in
the list. Because we’re starting at the first entry then we don’t need
to move if there is only one entry, so we need to subtract one from the
count to give us the number of “steps” to take from the first cell.
The screen shot illustrates how this is built up for a list in column A starting at cell A1. The function we would use is simply
However this does give us a
#REF! if the list has no entries -
which looks like an error if you give someone a blank list (essentially
because it is). So if you need to account for this, just include an