2

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?

CactusCake
  • 986
  • 3
  • 12
  • 32
  • are the time stamps always in chronological order? older ones coming first and then newer ones, right? – Marcel Nov 25 '14 at 16:06
  • 1
    For now, yes. The table is populated using a SQL query, so I have ordered them by the the values in col A descending, then the values in col B descending on the server before they get to Excel. I don't anticipate needing to order them any other way, so that is a safe assumption. – CactusCake Nov 25 '14 at 16:09
  • 2
    I know this is tagged as an Excel question, but it might be worth looking at http://stackoverflow.com/questions/5463384/sequentially-number-rows-by-keyed-group-in-sql for a SQL solution – Tom Sharpe Nov 25 '14 at 16:39
  • 1
    Good thinking @TomSharpe I went ahead and used that. Should I delete this question or leave it open? – CactusCake Nov 25 '14 at 17:00
  • I've got a quick Excel fix as well so plz leave it open if that's OK – Tom Sharpe Nov 25 '14 at 17:03
  • Yup, that's the quick fix I was thinking of that @pnuts just posted. I just wasn't quick enough! – Tom Sharpe Nov 25 '14 at 18:59
  • Yes, I guess I was overthinking it, these solutions are all fine. I work in an environment with a lot of "fiddlers" so I usually try to create formulas that won't mess up if someone decides to change the sort order one day (can't imagine why they would ever need to, but that does mean they wouldn't. And then of course they'd want to know why *my* sheet is giving them crazy numbers). The SQL solution is ideal because then the output is just a simple number and not formula driven. I will leave the answer selection up to community votes since I'm not in need of an Excel solution myself anymore. – CactusCake Nov 25 '14 at 19:21
  • @pnuts only joking, I don't mind at all and agree with your wise comments as always (have added another comment below on @AHC). Thank you for your comments Joe, glad it was helpful. – Tom Sharpe Nov 25 '14 at 20:31

1 Answers1

1

So here is the trick:

copy and paste in C1 and drag and fill down till end. 

   =IF(A2=A1;SUMPRODUCT(--(A$1:A$9=A1);--(B1>B$1:B$9))+1;SUMPRODUCT(--(A$1:A$9=A1);--(B1>B$1:B$9))+1)



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.

Marcel
  • 2,764
  • 1
  • 24
  • 40
  • I think the answer is very neat and I get that it works even if the dates are not in order but I'm not quite with it. I don't see why the SUMPRODUCT is repeated in the IF statement and why you can't just say =SUMPRODUCT(--(A$1:A$9=A1),--(B1>B$1:B$9))+1 , and also since you're using SUMPRODUCT why it needs to be an array formula. Could ties be a problem? I guess there aren't supposed to be any. – Tom Sharpe Nov 25 '14 at 20:25
  • actually the second sumproduct is there because we need the last item in the list, without it you get 1, 2, 3, and for the fourth one you get either nothing or 0. This corresponds to the **value if error** of the **if formula**. So we need it to pull the last item which is 4 (in the first set of your data). As for the **-** , no they should be there you can't remove them. If my answer is the one you wanted, then please accept it as the *answer* to the question. – Marcel Nov 25 '14 at 20:38
  • It must be included to pull the last item, without which the last item of the list will not appear. i have tested different version, and this is the correct one. – Marcel Nov 25 '14 at 20:52
  • 1
    I tend to find array formulas slower to calculate en masse than other options. I might just be bad at using them appropriately, either way, I usually steer clear if there is an alternative method. I appreciate the suggestion though. – CactusCake Nov 25 '14 at 21:10
  • @Joe if you are happy with the answer, i mean, if it answers your questions, then please accept it as an answer. – Marcel Nov 25 '14 at 21:23