Friends, I'm hoping you can help. I'm fairly certain I found a solution to this problem below a while ago, but silly me didn't write it down and now I can't remember how I did it. I'm drawing a blank on what to search for (Google is flooded with answers on "dynamic named ranges", which is not what I'm aiming for here). So, the question:
How can I define a range within a formula (say, RANK for example) that moves as I progress down through a table? I'm trying to avoid using INDIRECT, because it becomes a bit of a memory/processor hog when repeated throughout a large table. Pretty sure there is another way, maybe with INDEX or MATCH?
A simplified version of the data would appear as follows: Column A has a bunch of reference numbers, each one repeats a few times. Column B has a bunch of timestamps Column C is where I would like to rank the timestamp in column B, as compared to all other timestamps that share the same reference number in column A.
The result set should look like this:
| A | B | C |
| abc123 | 01/01/2014 12:30 | 1 |
| abc123 | 01/02/2014 12:30 | 2 |
| abc123 | 01/02/2014 13:30 | 3 |
| abc123 | 01/03/2014 09:30 | 4 |
| def456 | 01/01/2014 12:30 | 1 |
| def456 | 01/01/2014 12:45 | 2 |
| xyz987 | 01/02/2014 12:30 | 1 |
| xyz987 | 01/02/2014 16:30 | 2 |
| xyz987 | 01/03/2014 11:30 | 3 |
Any ideas on what would be the least taxing solution for the processor in this case?
So here is the trick:
copy and paste in C1 and drag and fill down till end.
this is an array formula, so press ctrl+shift+enter to calculate the formula
Here is the example sheet in this file downloadable from this link
P.S. remember to adjust the formula to your regional settings by replacing the ";" with "," . Have fun.