Finding Invoices Paid Out of Order

[Elliottj2121]

Hello,

I am hoping someone can help me. I have a basic understanding of VBA and formulas but this problem is stumping me. I am trying to have excel find the invoices that are paid out of order by invoice date. In the condensed example below Alpha Company skipped the invoice from 10/10/2022 and 10/24/2022. However, the invoices between rows 14-16 are not skipped because there are no newer invoices paid after row 13. Beta Company did not skip any invoices. What I would like is to run a macro (or use formulas with sorting) that only would show the skipped invoices, i.e. the rows in yellow. Each company has a unique customer number, my spreadsheet has thousands of rows and the number of rows can vary day to day.

Thank you!!!

SKIP_RPT_EX.xlsx
	A	B	C	D	E	F	G	H	I
1	CUST #	CUST NAME	INVOICE NUMBER	INV DATE	PAID DATE	OPEN AMT	PAY DAY OF YEAR	INVOICE DAY OF YEAR	PAID=1 OPEN=-1
2	1375	ALPHA	468	10/09/2022	11/03/2022	189.05	307	283	-1
3	1375	ALPHA	471	10/10/2022	NULL	189.05	0	283	-1
4	1375	ALPHA	944	10/11/2022	11/03/2022	0.00	307	284	1
5	1375	ALPHA	579	10/19/2022	11/03/2022	0.00	307	292	1
6	1375	ALPHA	68	10/20/2022	11/03/2022	0.00	307	293	1
7	1375	ALPHA	744	10/24/2022	11/03/2022	0.00	307	297	1
8	1375	ALPHA	41	10/24/2022	11/03/2022	0.00	307	297	1
9	1375	ALPHA	363	10/24/2022	NULL	346.73	0	297	-1
10	1375	ALPHA	504	10/24/2022	11/03/2022	0.00	307	297	1
11	1375	ALPHA	955	10/31/2022	11/03/2022	0.00	307	304	1
12	1375	ALPHA	447	10/31/2022	11/03/2022	0.00	307	304	1
13	1375	ALPHA	327	10/31/2022	11/03/2022	0.00	307	304	1
14	1375	ALPHA	151	11/01/2022	NULL	1197.34	0	305	-1
15	1375	ALPHA	153	11/01/2022	NULL	21511.83	0	305	-1
16	1375	ALPHA	150	11/02/2022	NULL	693.45	0	306	-1
17	1200	BETA	678	08/29/2022	11/03/2022	0.00	307	241	1
18	1200	BETA	119	08/29/2022	11/03/2022	0.00	307	241	1
19	1200	BETA	911	08/29/2022	11/03/2022	0.00	307	241	1
20	1200	BETA	526	09/21/2022	NULL	10621.70	0	264	-1
21	1200	BETA	861	09/21/2022	NULL	3829.19	0	264	-1
22	1200	BETA	957	09/22/2022	NULL	10291.68	0	265	-1
23	1200	BETA	240	09/26/2022	NULL	2252.56	0	269	-1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Cell Value	duplicates	text	NO

 

[Elliottj2121]

Hello

Just seeing if anyone can give me a hand with this problem. Much appreciated!

 

[Snakehips]

@Elliottj2121 As you have XL365 can you use something like below?
It is requiring a helper column.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	CUST #	CUST NAME	INVOICE NUMBER	INV DATE	PAID DATE	OPEN AMT	PAY DAY OF YEAR	INVOICE DAY OF YEAR	PAID=1 OPEN=-1	Helper
2	1375	ALPHA	468	9/10/22	3/11/22	189.0504	307	283	1	13		CUST #	CUST NAME	INVOICE NUMBER
3	1375	ALPHA	471	10/10/22	NULL	189.0504	0	283	-1	13		1375	ALPHA	471
4	1375	ALPHA	944	11/10/22	3/11/22	0	307	284	1	13		1375	ALPHA	363
5	1375	ALPHA	579	10/19/2022	3/11/22	0	307	292	1	13
6	1375	ALPHA	68	10/20/2022	3/11/22	0	307	293	1	13
7	1375	ALPHA	744	10/24/2022	3/11/22	0	307	297	1	13
8	1375	ALPHA	41	10/24/2022	3/11/22	0	307	297	1	13
9	1375	ALPHA	363	10/24/2022	NULL	346.7268	0	297	-1	13
10	1375	ALPHA	504	10/24/2022	3/11/22	0	307	297	1	13
11	1375	ALPHA	955	10/31/2022	3/11/22	0	307	304	1	13
12	1375	ALPHA	447	10/31/2022	3/11/22	0	307	304	1	13
13	1375	ALPHA	327	10/31/2022	3/11/22	0	307	304	1	13
14	1375	ALPHA	151	1/11/22	NULL	1197.3444	0	305	-1	13
15	1375	ALPHA	153	1/11/22	NULL	21511.8288	0	305	-1	13
16	1375	ALPHA	150	2/11/22	NULL	693.4536	0	306	-1	13
17	1200	BETA	678	08/29/2022	3/11/22	0	307	241	1	19
18	1200	BETA	119	08/29/2022	3/11/22	0	307	241	1	19
19	1200	BETA	911	08/29/2022	3/11/22	0	307	241	1	19
20	1200	BETA	526	09/21/2022	NULL	10621.6992	0	264	-1	19
21	1200	BETA	861	09/21/2022	NULL	3829.1904	0	264	-1	19
22	1200	BETA	957	09/22/2022	NULL	10291.68	0	265	-1	19
23	1200	BETA	240	09/26/2022	NULL	2252.5608	0	269	-1	19
Sheet5

Cell Formulas
Range	Formula
L2:N4	L2	=FILTER(A:C,(I:I<>1)*(ROW(I:I)<J:J))
J2:J23	J2	=MAX((I:I=1)*ROW(I:I)*(A:A=A2))
Dynamic array formulas.

Hope that helps.

 

[Peter_SSs]

I am unsure whether you wanted those invoices highlighted like in post #1 or listed as Tony has done or something else. Anyway, here are a couple more possible options for you. I would avoid using whole column references if possible.

Elliottj2121.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	CUST #	CUST NAME	INVOICE NUMBER	INV DATE	PAID DATE	OPEN AMT	PAY DAY OF YEAR	INVOICE DAY OF YEAR	PAID=1 OPEN=-1			CUST #	CUST NAME	INVOICE NUMBER	INV DATE	PAID DATE	OPEN AMT	PAY DAY OF YEAR	INVOICE DAY OF YEAR	PAID=1 OPEN=-1
2	1375	ALPHA	468	9/10/2022	3/11/2022	189.0504	307	283	-1			1375	ALPHA	471	10/10/2022	NULL	189.0504	0	283	-1
3	1375	ALPHA	471	10/10/2022	NULL	189.0504	0	283	-1			1375	ALPHA	363	24/10/2022	NULL	346.7268	0	297	-1
4	1375	ALPHA	944	11/10/2022	3/11/2022	0	307	284	1
5	1375	ALPHA	579	19/10/2022	3/11/2022	0	307	292	1
6	1375	ALPHA	68	20/10/2022	3/11/2022	0	307	293	1
7	1375	ALPHA	744	24/10/2022	3/11/2022	0	307	297	1
8	1375	ALPHA	41	24/10/2022	3/11/2022	0	307	297	1
9	1375	ALPHA	363	24/10/2022	NULL	346.7268	0	297	-1
10	1375	ALPHA	504	24/10/2022	3/11/2022	0	307	297	1
11	1375	ALPHA	955	31/10/2022	3/11/2022	0	307	304	1
12	1375	ALPHA	447	31/10/2022	3/11/2022	0	307	304	1
13	1375	ALPHA	327	31/10/2022	3/11/2022	0	307	304	1
14	1375	ALPHA	151	1/11/2022	NULL	1197.3444	0	305	-1
15	1375	ALPHA	153	1/11/2022	NULL	21511.8288	0	305	-1
16	1375	ALPHA	150	2/11/2022	NULL	693.4536	0	306	-1
17	1200	BETA	678	29/08/2022	3/11/2022	0	307	241	1
18	1200	BETA	119	29/08/2022	3/11/2022	0	307	241	1
19	1200	BETA	911	29/08/2022	3/11/2022	0	307	241	1
20	1200	BETA	526	21/09/2022	NULL	10621.6992	0	264	-1
21	1200	BETA	861	21/09/2022	NULL	3829.1904	0	264	-1
22	1200	BETA	957	22/09/2022	NULL	10291.68	0	265	-1
23	1200	BETA	240	26/09/2022	NULL	2252.5608	0	269	-1
Sheet1

Cell Formulas
Range	Formula
L2:T3	L2	=FILTER(A2:I9999,(E2:E9999="NULL")*(COUNTIFS(B2:B9999,B2:B9999,D2:D9999,">"&D2:D9999,I2:I9999,1)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:I23	Expression	=AND($E2="NULL",COUNTIFS($B$2:$B$9999,$B2,$D$2:$D$9999,">"&$D2,$I$2:$I$9999,1))	text	NO

 

[Elliottj2121]

I am unsure whether you wanted those invoices highlighted like in post #1 or listed as Tony has done or something else. Anyway, here are a couple more possible options for you. I would avoid using whole column references if possible.

Elliottj2121.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	CUST #	CUST NAME	INVOICE NUMBER	INV DATE	PAID DATE	OPEN AMT	PAY DAY OF YEAR	INVOICE DAY OF YEAR	PAID=1 OPEN=-1			CUST #	CUST NAME	INVOICE NUMBER	INV DATE	PAID DATE	OPEN AMT	PAY DAY OF YEAR	INVOICE DAY OF YEAR	PAID=1 OPEN=-1
2	1375	ALPHA	468	9/10/2022	3/11/2022	189.0504	307	283	-1			1375	ALPHA	471	10/10/2022	NULL	189.0504	0	283	-1
3	1375	ALPHA	471	10/10/2022	NULL	189.0504	0	283	-1			1375	ALPHA	363	24/10/2022	NULL	346.7268	0	297	-1
4	1375	ALPHA	944	11/10/2022	3/11/2022	0	307	284	1
5	1375	ALPHA	579	19/10/2022	3/11/2022	0	307	292	1
6	1375	ALPHA	68	20/10/2022	3/11/2022	0	307	293	1
7	1375	ALPHA	744	24/10/2022	3/11/2022	0	307	297	1
8	1375	ALPHA	41	24/10/2022	3/11/2022	0	307	297	1
9	1375	ALPHA	363	24/10/2022	NULL	346.7268	0	297	-1
10	1375	ALPHA	504	24/10/2022	3/11/2022	0	307	297	1
11	1375	ALPHA	955	31/10/2022	3/11/2022	0	307	304	1
12	1375	ALPHA	447	31/10/2022	3/11/2022	0	307	304	1
13	1375	ALPHA	327	31/10/2022	3/11/2022	0	307	304	1
14	1375	ALPHA	151	1/11/2022	NULL	1197.3444	0	305	-1
15	1375	ALPHA	153	1/11/2022	NULL	21511.8288	0	305	-1
16	1375	ALPHA	150	2/11/2022	NULL	693.4536	0	306	-1
17	1200	BETA	678	29/08/2022	3/11/2022	0	307	241	1
18	1200	BETA	119	29/08/2022	3/11/2022	0	307	241	1
19	1200	BETA	911	29/08/2022	3/11/2022	0	307	241	1
20	1200	BETA	526	21/09/2022	NULL	10621.6992	0	264	-1
21	1200	BETA	861	21/09/2022	NULL	3829.1904	0	264	-1
22	1200	BETA	957	22/09/2022	NULL	10291.68	0	265	-1
23	1200	BETA	240	26/09/2022	NULL	2252.5608	0	269	-1
Sheet1

Cell Formulas
Range	Formula
L2:T3	L2	=FILTER(A2:I9999,(E2:E9999="NULL")*(COUNTIFS(B2:B9999,B2:B9999,D2:D9999,">"&D2:D9999,I2:I9999,1)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:I23	Expression	=AND($E2="NULL",COUNTIFS($B$2:$B$9999,$B2,$D$2:$D$9999,">"&$D2,$I$2:$I$9999,1))	text	NO

I get a #CALC! error with the fist formula.

 

[Peter_SSs]

I get a #CALC! error with the fist formula.

Do you mean the L2 formula?
Test by starting a blank worksheet and copy my mini sheet using this icon:

and paste into cell A1 of the blank worksheet. Does that produce the results shown in my mini sheet?

 

[Elliottj2121]

Do you mean the L2 formula?
Test by starting a blank worksheet and copy my mini sheet using this icon: View attachment 77951

and paste into cell A1 of the blank worksheet. Does that produce the results shown in my mini sheet?

Yes with the L2 formula and I did what you suggested first. I'm guessing since my actual data set is around 30,000 rows I need to use the is_blank argument.

Also I can't seem to get the conditional formatting right.

 

[Peter_SSs]

Yes with the L2 formula and I did what you suggested first.

Did that work for the smaller sample size?

I'm guessing since my actual data set is around 30,000 rows I need to use the is_blank argument.

Can you explain why you would need is blank and where you would need it?

Also I can't seem to get the conditional formatting right.

So, are you looking for the "conditional formatting method" (results could be scattered d throughout 30,000 rows) or are you looking for the "list method" where all the results would be listed together?

 

[Elliottj2121]

Did that work for the smaller sample size?


Can you explain why you would need is blank and where you would need it?


So, are you looking for the "conditional formatting method" (results could be scattered d throughout 30,000 rows) or are you looking for the "list method" where all the results would be listed together?

I would prefer the highlighted method so it can be used with filter by color as that is most familiar with the staff that will be using it. A new list would be exported from the database daily and rows will vary.

 

[Peter_SSs]

Sounds like the size of the data is causing some problems. In that case I suggest that you abandon the Conditional Formatting idea. Conditional Formatting is (super) volatile so will be placing more calculation burden on the workbook.

What about adding a helper column as I have done below in column J. Instead of filtering on colour, you can filter that column for 1.

If you really do want the CF then I have included a much simpler one in my mini sheet below using that extra column, but I don't think that really adds much over a 30,000 row data set. Anyway, you can try with/without the CF if you want.

Elliottj2121.xlsm
	A	B	C	D	E	F	G	H	I	J
1	CUST #	CUST NAME	INVOICE NUMBER	INV DATE	PAID DATE	OPEN AMT	PAY DAY OF YEAR	INVOICE DAY OF YEAR	PAID=1 OPEN=-1	Skipped
2	1375	ALPHA	468	9/10/2022	3/11/2022	189.0504	307	283	-1
3	1375	ALPHA	471	10/10/2022	NULL	189.0504	0	283	-1	1
4	1375	ALPHA	944	11/10/2022	3/11/2022	0	307	284	1
5	1375	ALPHA	579	19/10/2022	3/11/2022	0	307	292	1
6	1375	ALPHA	68	20/10/2022	3/11/2022	0	307	293	1
7	1375	ALPHA	744	24/10/2022	3/11/2022	0	307	297	1
8	1375	ALPHA	41	24/10/2022	3/11/2022	0	307	297	1
9	1375	ALPHA	363	24/10/2022	NULL	346.7268	0	297	-1	1
10	1375	ALPHA	504	24/10/2022	3/11/2022	0	307	297	1
11	1375	ALPHA	955	31/10/2022	3/11/2022	0	307	304	1
12	1375	ALPHA	447	31/10/2022	3/11/2022	0	307	304	1
13	1375	ALPHA	327	31/10/2022	3/11/2022	0	307	304	1
14	1375	ALPHA	151	1/11/2022	NULL	1197.3444	0	305	-1
15	1375	ALPHA	153	1/11/2022	NULL	21511.8288	0	305	-1
16	1375	ALPHA	150	2/11/2022	NULL	693.4536	0	306	-1
17	1200	BETA	678	29/08/2022	3/11/2022	0	307	241	1
18	1200	BETA	119	29/08/2022	3/11/2022	0	307	241	1
19	1200	BETA	911	29/08/2022	3/11/2022	0	307	241	1
20	1200	BETA	526	21/09/2022	NULL	10621.6992	0	264	-1
21	1200	BETA	861	21/09/2022	NULL	3829.1904	0	264	-1
22	1200	BETA	957	22/09/2022	NULL	10291.68	0	265	-1
23	1200	BETA	240	26/09/2022	NULL	2252.5608	0	269	-1
Sheet1 (2)

Cell Formulas
Range	Formula
J2:J23	J2	=IF(E2="NULL",IF(COUNTIFS(B$2:B$40000,B2,D$2:D$40000,">"&D2,I2:I40000,1),1,""),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:I23	Expression	=$J2	text	NO