For each row in a group, compare creation date with not outstanding date for all previous.

[rebecca_w_s]

Hi.

I am looking at historic invoice data for different customers and I want to calculate for each invoice: “number of previous invoices still outstanding on creation”.

Thus, I have to compare the creation date of an invoice with the column “not outstanding date” (which I have created) for all previous invoices for that customer.

I also have some customers where multiple invoices are created on the same date.

I am assuming I need to create an iterative Macro?

Here is some example data:

Customer	Invoice Number	date_created	not_outstanding_date
customer1	1	03.09.2015	08.09.2015
customer1	2	06.09.2015	04.10.2015
customer1	3	19.09.2015	09.10.2015
customer1	4	24.09.2015	16.10.2015
customer2	1	23.01.2017	09.02.2017
customer2	2	10.02.2017	02.03.2017
customer2	3	14.02.2017	06.03.2017
customer2	4	28.02.2017	17.03.2017
customer2	5	28.02.2017	17.03.2017

What I want to get:

Customer	Invoice Number	date_created	not_outstanding_date	num_outstanding_on_creation
customer1	1	03.09.2015	08.09.2015	0
customer1	2	06.09.2015	04.10.2015	1
customer1	3	19.09.2015	09.10.2015	1
customer1	4	24.09.2015	16.10.2015	2
customer2	1	23.01.2017	09.02.2017	0
customer2	2	10.02.2017	02.03.2017	0
customer2	3	14.02.2017	06.03.2017	1
customer2	4	28.02.2017	17.03.2017	2
customer2	5	28.02.2017	17.03.2017	2

[jdunkerley79 · Accepted solution]

The easiest way I can think of is to join the data to itself on the Customer field

You can then filter where Invoice numbers are equal and the date_created being between the range of the second invoice

After that summarise to get the count. This will exclude ones with 0 matches so you need to add those back in.

Sample attached

[rebecca_w_s]

This works perfectly. Thank you.