1

I have an excel spreadsheet with 250 accounts, each with a bunch of transaction records. I need to extract a date where an interest rate has changed. There are 250000+ records.

It looks like this:

ACCT                    CEL-INT-RATE    LTD-INT-BILL    FileDate
0006365290140074793     84.00           1479.43         20131007
0006365290140074793     84.00           1479.43         20131012
0006365290140074793     84.00           1479.43         20131014
0006365290140074793     84.00           2598.55         20131107 <---
0006365290140074793     21.00           2598.55         20131111
0006365290140074793     21.00           2598.55         20131129
0006365290140074793     21.00           2598.55         20131204

I need the date '20131107', which I marked with <---, where the interest rate was last at 84.00, before changing to 21.00, for the account 0006365290140074793

Can anyone help with this formula.

Thanks in advance

Alex P
  • 12,249
  • 5
  • 51
  • 70
6dev6il6
  • 767
  • 2
  • 15
  • 34

1 Answers1

5

This is more of a power user function than a coding one but... '=IF(A2=A3,IF(B3<B2,"<---",""),"")

Used this fill down for a column and the <--- will identify when rate goes from 84 to 21 as indicated in sample data.

enter image description here However I'm not sure what you mean by extract... is this a one time thing or something that needs to be automated? or just quickly identified? too many unknowns to really look deep into this. What's the use case? How does this need to work and what are the expected results?

AMENDED:

A welcome comment pointed out that the filedate being returned was that of the lower int-rate when the upper date was requested. This can be achieved by altering the formula as indicated in '=IF(A2=A3,IF(B2>B3,"<---",""),"") or visually: enter image description here

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Thanks for your answer. This is a one time thing. By extract I mean the results should be 250 dates(1 for each account) where the interest changed. The use case (I think this is what you want) is accounts were overcharged by incorrect interest amount and need to be refunded. The interest rates were corrected but not refunded and we need to work out the dates that interest was changed in order to calculate the overcharged interest. ie before that date they were being overcharged. – 6dev6il6 May 23 '14 at 15:36
  • So by using the formula listed, (adjusted for your columns) fill down and then filter on it, you should get the desired results. ASSUMING that there is only once instance per account going from 84 to 21 per account and that the records are properly sorted. (Otherwise you may get no records, or you may get multiple for the same account if multiple occurrences of a change occur) – xQbert May 23 '14 at 15:38
  • Yes, per account there is only one instance going from 84 to 21. But there are many accounts that go from 22 to 21, but only once per account. 84 was the largest incorrect rate There are a lot of different rates so I cannot hard code 84 or 21 as in your formula. The only way to 'notice' this is when the account number is the same and in the next row the interest is less that the interest in the row above – 6dev6il6 May 23 '14 at 15:41
  • Updated image and formula Note though if the same account drops twice, both are marked. When the account number changes if a decrease in rate exists, it doesn't get marked. Note filtering by the here it is column will still flag all instances of a decrease and filling formula down will allow you to filter finding all instances. – xQbert May 23 '14 at 15:53
  • `=IF(A2=A3,IF(B2>B3,"<---",""),"")` Thanks I'll amend answer to include both possibilities – xQbert May 23 '14 at 17:32
  • 1
    @pnuts no trouble at all. I'd rather answer the question asked correctly incase it helps others. If the OP needed the original answer then so be it, if they needed the second, well now both bases are covered. – xQbert May 23 '14 at 20:38