Return Date range for Unique Payments

[kevdragon1]

Hi,

I have a data set made of 2 columns for each office.

Column A has the Payments.
Column B has the Date of the payments (1st of the month).

My goal is to have a summary (dates between which the payments will be made).

Here is an example :

Payment	Date
1000	Dec 2020
1000	Jan 2021
1500	Feb 2021
1700	Mar 2021
1700	April 2021
1000	May 2021
1000	June 2021
0	July 2021

The result I would expect is in an excel format :

Payment	Start	End
1000	Dec 2020	Jan 2021
1500	Feb 2021	Feb 2021
1700	Mar 2021	April 2021
1000	May 2021	June 2021
0	July 2021	July 2021

The dates are always from small (earliest) to big (latest). The dates do not get duplicated.

The problem is the payments. In 90% of the case, the offices have bigger payments and the payments don't repeat.
My problem is that sometimes the payments will go (as shown above) like this: 1000$ for month 1, 2000$ for month 2 and 3, and then back down to 1000$ for month 4.

I tried coding a Macro with the UNIQUE formula for the payments and then using MIN and MAX to find the dates for each payment. That worked great until I realized that sometimes a payment will get repeated down the road...

Is there a formula that can help me? I think what needs to happen is for the Macro to understand that it needs to check each repeating payment row and return the dates and then do that for the whole column. But I have no idea how to do that.

 

[jasonb75]

I notice that you mention trying UNIQUE so I'm going to assume that you are using office 365 and not excel 2019 as shown on your profile.

Book1
	A	B	C	D	E	F
1	Payment	Date		Payment	Start	End
2	1000	Dec-20		1000	Dec-20	Jan-21
3	1000	Jan-21		1500	Feb-21	Feb-21
4	1500	Feb-21		1700	Mar-21	Apr-21
5	1700	Mar-21		1000	May-21	Jun-21
6	1700	Apr-21		0	Jul-21	Jul-21
7	1000	May-21
8	1000	Jun-21
9	0	Jul-21
Sheet2

Cell Formulas
Range	Formula
D2:E6	D2	=FILTER(A2:B9,A2:A9<>A1:A8)
F2:F6	F2	=IFERROR(EOMONTH(E3,-1),E2)
Dynamic array formulas.

 

[kevdragon1]

I notice that you mention trying UNIQUE so I'm going to assume that you are using office 365 and not excel 2019 as shown on your profile.

Book1
	A	B	C	D	E	F
1	Payment	Date		Payment	Start	End
2	1000	Dec-20		1000	Dec-20	Jan-21
3	1000	Jan-21		1500	Feb-21	Feb-21
4	1500	Feb-21		1700	Mar-21	Apr-21
5	1700	Mar-21		1000	May-21	Jun-21
6	1700	Apr-21		0	Jul-21	Jul-21
7	1000	May-21
8	1000	Jun-21
9	0	Jul-21
Sheet2

Cell Formulas
Range	Formula
D2:E6	D2	=FILTER(A2:B9,A2:A9<>A1:A8)
F2:F6	F2	=IFERROR(EOMONTH(E3,-1),E2)
Dynamic array formulas.

Hi,

Ah...I updated that, yes I am using 365.

Thank you for the fast response. Did you copy-paste the start dates? Is there a way to get them from a formula?

Edit: facepalm...the filter function returns the start date....let me see if this is working thx

 

[kevdragon1]

I notice that you mention trying UNIQUE so I'm going to assume that you are using office 365 and not excel 2019 as shown on your profile.

Book1
	A	B	C	D	E	F
1	Payment	Date		Payment	Start	End
2	1000	Dec-20		1000	Dec-20	Jan-21
3	1000	Jan-21		1500	Feb-21	Feb-21
4	1500	Feb-21		1700	Mar-21	Apr-21
5	1700	Mar-21		1000	May-21	Jun-21
6	1700	Apr-21		0	Jul-21	Jul-21
7	1000	May-21
8	1000	Jun-21
9	0	Jul-21
Sheet2

Cell Formulas
Range	Formula
D2:E6	D2	=FILTER(A2:B9,A2:A9<>A1:A8)
F2:F6	F2	=IFERROR(EOMONTH(E3,-1),E2)
Dynamic array formulas.

Could you please explain this part of the formula in D2 : A2:A9<>A1:A8...You are saying return the column A that doesn't contain column A? I am a bit confused sorry. Why are you shifting by 1?

 

[jasonb75]

By shifting 1 row it is looking for rows in column A that don't match the row above.

A2<>A1, A3<>A2, A4<>A3, etc..

 

[jasonb75]

I wasn't able to make the end date formula spill due to the last row in the source table having a zero payment amount which has the same numeric value as the empty cell below it.
The mini sheet below has an end date formula that will spill but as you can see the final end date is missing. With a payment other than 0 in A9 the final end date shows correctly.

Book1
	A	B	C	D	E	F
1	Payment	Date		Payment	Start	End
2	1000	Dec-20		1000	Dec-20	Jan-21
3	1000	Jan-21		1500	Feb-21	Feb-21
4	1500	Feb-21		1700	Mar-21	Apr-21
5	1700	Mar-21		1000	May-21	Jun-21
6	1700	Apr-21		0	Jul-21
7	1000	May-21
8	1000	Jun-21
9	0	Jul-21
Sheet2

Cell Formulas
Range	Formula
D2:E6	D2	=FILTER(A2:B9,A2:A9<>A1:A8)
F2:F5	F2	=FILTER(B2:B9,A2:A9<>A3:A10)
Dynamic array formulas.