A finesse you can apply with the LARGE() function is to consider the case where you have rows with the same value, e.g:
The LARGE(A1:A5,1) function will simply tell you that 3 is the largest value. But you may actually want the top three rows, e.g. to return the set (Fred, Nigel, Ian)
The way to achieve this is to add some unique key value to each ranking value so that they become distinct. In the example above, we could create a new column with value as follows:
This will then give us:
A B C
1 James 1.000001
3 Fred 3.000002
2 Ian 2.000003
1 Graham 1.000004
3 Nigel 3.000005
If we run the LARGE() function against Column C, we now have our unique values, which we can then feed into a VLOOKUP to return the person’s name.
Was banging my head against the wall a bit tonight trying to close my ADODB.Connection object down cleanly. My mistake was writing something like the following:
If Not objConnection Is Nothing Then objConnection.Close
It seemed that objConnection was both not nothing, and throwing an error about already being closed.
Solution was to check the State property first, so now I have:
If objConnection.State <> adoStateClosed Then objConnection.Close
Used this to find the top 5 of 200 values. Never know you could do this before!
I vaguely knew you could sum across worksheets before, but cool to rediscover this.