Forum Discussion
Working out when a regular subscription changed from transaction lists
if a person increased the subsciption level, but reduces the Number of subsruiptions. ie Was paying £40 (20X2) and changed to £22 (22x1)
Not sure I understand:
- Could you post a sample with the expected result?
- What version of Excel do you run + on which OS (Windows/Mac)?
I run windows and office 365
The amount of data lines are also big. Will have (up to 100,0000 lines of data) will that be an issue?
So below Bob had two subsciptions at £20 (paid £40) he increased to £22 on the 2/3/23 so that is wat we want to see.
However as he reduced his overall payents the formula looks for the increase.
I might be able to get the number of subsciption in, and hence devide £40 by 2.
But is there a formula that can find the last change (up or down)
up to 100,0000 lines of data => I would recommend you do it with Power Query
I assumed your Dates are sorted/ordered as shown in your picture, otherwise a change must be made to the query...
Could you test the attached query with some more data and let me know of any issue?
(Put your data in the table sheet Input > Switch to sheet Output > Right-click in the Green table > Refresh)
The Power Query works. I have done a few power querys in my time, but the code for that looks very simple compared to my long work arounds.
The problem is translating this in to the real life model.
Bit more backgorund, we are a charity and we have 2000 children sponsored.
We have a legacy scheme where people paid up to £18, and the we introduced a new sheme at £22.
We then did a price increase to £24, and we are planning another to £26.
However what we are trying to do is encourage people paying:
£18 to increase to £22 (to big a gap to go to £24 or £26)
and those paying £22 and £24 to increase to £26.
However some people recently increased from £18 to £22 and we dont want to ask them to increase again. So we are trying to establish when each person increased (the software doesn't tell us so we have to go by transactions)
This would be simple if it was 1:1 the formulaes shoud work fine.. (There is around 1400 that are 1:1)
However, what I am now finding is that some people with multipl children have 2 seperate Payments.
One can be paying £18 and (for one child) and £22 for another.
In reality there was no price change, because they have paid the same, but because its the same name (or record number) its picking that they have increased.