I came across this video from Paula Guilfoyle today via LinkedIn. How To Find the Nth Largest in Excel.
There’s a couple of questions I would ask.
- Why do we need to find the Nth item? (read: is this a one-off?)
- Where is the data located, and why is it located there?
When I ask this, I usually get a mixture of a blank expression and a little ‘why are we asking this?’
Fair question. In a few cases this really is a one-off, and it must be done quickly. And the data is on a spreadsheet because Bob emailed it to me 🙁
But I bet in 99% of the time it’s not like this. It is workflow.
Business runs on a flow of processes, handled by people. If something is a one-off it’s the job of process management to ask the question ‘why is it’?
Assuming the requirement is part of the regular workflow
The rest of the article assumes that this requirement is part of the regular workflow. The flow that runs every day in the business process.
If so, a manual approach is inappropriate. See my vlog The Circle Line [TBA] which examines the ‘pass the parcel’ approach to workflow. And also the ‘Henry Ford production line’. [TBA]
If so,
- the data could be different each time.
- the ordinal number (the Nth) could be different each time.
- the data will be located in a central location in a table, accessible by spreadsheets.
A brief overview of how we could set this up
The way I see it:
- Pull the data into memory, already sorted. This could be a Recordset.
- Loop through the Recordset, with a counter.
- Read the Nth item. Place it where it is needed.
These steps can be fully automated.
Whereas Paula’s method is for a one-off, and therefore manual.
How can we learn this?
- Learn that a Recordset is a place where data can placed in Excel’s memory.
- Learn what holding data in memory is about. (1974 we had memory calculators. Why have we forgotten this?)
- Learn the characteristics of a Recordset. That we can loop through it from beginning to end. It is similar to a range of cells, but it’s not a sheet.
- We can pull data into a Recordset already in a required order, already filtered, as required.
- We can read data from the Recordset, and place it in a variety of places. eg. on a sheet, in a dropdown etc.
- Working with data in memory is very fast.
- We can’t ‘see’ a Recordset, any more than we could ‘see’ the memory in a memory calculator.
In principle, this is the same as Paula’s technique where she shows you how to (1) put the list in the sort order and (2) use OFFSET to select the Nth item, N being entered in a cell.
My method here is on how to make it an automated (or, automatable) step in a larger workflow – which as I’ve suggested at the beginning, is a more realistic scenario in an organisation.
TO BE CONTINUED …
Add comment