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]

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

Thank you! This looks like its going to work. I have to retool the data thats exported but this is sound.

 

[Peter_SSs]

Cheers. Hope it works out for you.