Forum Discussion
Working out when a regular subscription changed from transaction lists
We have around 2000 subscribers, who are paying various amounts for a few services. What we need to do is find out when they upgraded thier recent subscription. This should be when their payments increased, Eg Form £20 to £22. They may also have multiple subscripstions. EG Pay £60 (£20x3) and changed to £66.
Eg in the tabe below that I would like to deduce is
- Joe moved to £22 on 1st April
- Sam moved to £44 on the 1st Feb
Can anyone help with a formula?
| Transaction | Record | Name | Date | Amount |
| 1 | 1 | Joe | 01-Jan | £20 |
| 2 | 1 | Joe | 01-Feb | £20 |
| 3 | 1 | Joe | 01-Mar | £20 |
| 4 | 1 | Joe | 01-Apr | £22 |
| 5 | 2 | Sam | 01-Jan | £40 |
| 6 | 2 | Sam | 01-Feb | £42 |
| 7 | 2 | Sam | 01-Mar | £42 |
| 8 | 2 | Sam | 01-Apr | £42 |
11 Replies
- Thanks you for your really fast reply - I now have two solutions that work, thanks to the MS community. I fear however when I throw in some live data there might be some annomilies. (like if a person increased the subsciption level, but reduces the Number of subsruiptions. ie Was paying £40 (20X2) and changed to £22 (22x1)
=INDEX($D$2:$D$18,MATCH(1,($C$2:$C$18=G2)*($E$2:$E$18<>$E$3:$E$19),0)+1)You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- Can the new amount also be pulled back?
=INDEX($E$2:$E$18,MATCH(1,($C$2:$C$18=G2)*($E$2:$E$18<>$E$3:$E$19),0)+1)Yes that's possible with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
