Merging Duplicate values and deleting rows based on certain parameters.

[JohnBecks]

Hi there,

I need to use VBA to change Table A to Table B based on the following rules:

If customers have positive values in Column I for 22 days or more, their rows in Columns A-D will be merged into four cells (e.g. Customer A & C).
If a customer has negative and positive values in Column I for 22 days or more, their rows in Columns A-D will be merged into four cells (e.g. Customer J).
If a customer has no positive values in Column I for 22 days or more, their entire row will be deleted (e.g. Customers B & E).
If a customer has a negative balance in Column D, their entire row will be deleted, regardless of the values in Column I (e.g. Customer G).
If a customer has only negative values in Column I for 22 days or more and no positive values in Column I, their entire row will be deleted (e.g. Customer H).

Appreciate all the help with this.

+22 Days (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Table A:
2
3	Customer	Limit	Terms	Balance	Invoice	Date	Due on	Days	Value	Matched	Outstanding
4	A	-	IMD	300.00	INSOP/00454900	13/01/2020	13/01/2020	22	1,696.59	1,433.57	263.02
5	A	-	IMD	300.00	INSOP/00454921	13/01/2020	13/01/2020	5	36.98	-	36.98
6	B	-	IMD	356.93	ARD6/00052235	10/10/2022	10/10/2022	10	356.93	-	356.93
7	C	25,000.00	IMD	9,014.37	RJNL2/00002721	06/09/2018	06/09/2018	1685	902.67	-	902.67
8	C	25,000.00	IMD	9,014.37	RJNL2/00002719	23/08/2018	23/08/2018	1699	1,640.76	-	1,640.76
9	C	25,000.00	IMD	9,014.37	RJNL2/00002722	06/09/2018	06/09/2018	1685	146.30	-	146.30
10	C	25,000.00	IMD	9,014.37	RJNL2/00002723	06/09/2018	06/09/2018	1685	1,951.98	-	1,951.98
11	C	25,000.00	IMD	9,014.37	RJNL2/00002724	30/08/2018	30/08/2018	1692	1,612.05	-	1,612.05
12	C	25,000.00	IMD	9,014.37	RJNL2/00002726	23/08/2018	23/08/2018	1699	455.75	-	455.75
13	C	25,000.00	IMD	9,014.37	RJNL2/00002725	30/08/2018	30/08/2018	1692	2,156.55	-	2,156.55
14	C	25,000.00	IMD	9,014.37	RJNL2/00002720	30/08/2018	30/08/2018	1692	148.31	-	148.31
15	D	-	IMD	2,649.00	RJNL2/00002649	05/11/2018	05/11/2018	1625	3,006.99	2,569.42	437.57
16	D	-	IMD	2,649.00	RJNL2/00002650	12/11/2018	12/11/2018	1618	2,211.43	-	2,211.43
17	E	-	COD	9,223.98	INSOP/01521179	12/04/2023	15/04/2023	3	8,871.95	2,960.27	5,911.68
18	E	-	COD	9,223.98	INSOP/01521185	12/04/2023	15/04/2023	3	3,312.30	-	3,312.30
19	F	-	IMD	30,649.26	RJNL2/00002760	14/06/2018	14/06/2018	1769	1,660.58	-	1,660.58
20	F	-	IMD	30,649.26	RJNL2/00002761	21/06/2018	21/06/2018	1762	198.75	-	198.75
21	F	-	IMD	30,649.26	RJNL2/00002762	21/06/2018	21/06/2018	1762	618.26	-	618.26
22	F	-	IMD	30,649.26	RJNL2/00002759	14/06/2018	14/06/2018	1769	398.71	-	398.71
23	F	-	IMD	30,649.26	RJNL2/00002768	13/09/2018	13/09/2018	1678	1,534.71	-	1,534.71
24	F	-	IMD	30,649.26	RJNL2/00002767	16/08/2018	16/08/2018	1706	2,466.84	-	2,466.84
25	F	-	IMD	30,649.26	RJNL2/00002752	24/05/2018	24/05/2018	1790	2,096.37	-	2,096.37
26	F	-	IMD	30,649.26	RJNL2/00002766	09/08/2018	09/08/2018	1713	1,935.54	-	1,935.54
27	F	-	IMD	30,649.26	RJNL2/00002750	17/05/2018	17/05/2018	1797	1,897.63	-	1,897.63
28	G	-	30	- 10,000.00	CRDN000398888	24/05/2018	24/05/2018	1790	- 5,000.00
29	G	-	30	- 10,000.00	RJNL2/00002769	09/08/2018	09/08/2018	1713	15,000.00
30	H	10,000.00	151	5,000.00	2PCRNR/00141135	12/03/2023	12/03/2023	35	- 2,500.00	-	-
31	H	10,000.00	151	5,000.00	2PCRNR/00141432	09/03/2023	14/03/2023	5	7,500.00	-	-
32	I	25,000.00	IMD	9,014.37	RJNL2/00002720	30/08/2018	30/08/2018	1692	- 148.31	-	148.31
33	J	-	IMD	5,000.00	RJNL2/00002649	05/11/2018	05/11/2018	1625	- 2,500.00	-43349
34	J	-	IMD	5,000.00	RJNL2/00002650	12/11/2018	12/11/2018	1618	7,500.00	-
35
36	Table B:
37
38	Customer	Limit	Terms	Balance	Invoice	Date	Due on	Days	Value	Matched	Outstanding
39	A	-	IMD	300.00	INSOP/00454900	13/01/2020	13/01/2020	22	1,696.59	1,433.57	263.02
40					INSOP/00454921	13/01/2020	13/01/2020	5	36.98	-	36.98
41	C	25,000.00	IMD	9,014.37	RJNL2/00002721	06/09/2018	06/09/2018	1685	902.67	-	902.67
42					RJNL2/00002719	23/08/2018	23/08/2018	1699	1,640.76	-	1,640.76
43					RJNL2/00002722	06/09/2018	06/09/2018	1685	146.30	-	146.30
44					RJNL2/00002723	06/09/2018	06/09/2018	1685	1,951.98	-	1,951.98
45					RJNL2/00002724	30/08/2018	30/08/2018	1692	1,612.05	-	1,612.05
46					RJNL2/00002726	23/08/2018	23/08/2018	1699	455.75	-	455.75
47					RJNL2/00002725	30/08/2018	30/08/2018	1692	2,156.55	-	2,156.55
48					RJNL2/00002720	30/08/2018	30/08/2018	1692	148.31	-	148.31
49	D	-	IMD	2,649.00	RJNL2/00002649	05/11/2018	05/11/2018	1625	3,006.99	2,569.42	437.57
50					RJNL2/00002650	12/11/2018	12/11/2018	1618	2,211.43	-	2,211.43
51	F	-	IMD	30,649.26	RJNL2/00002760	14/06/2018	14/06/2018	1769	1,660.58	-	1,660.58
52					RJNL2/00002761	21/06/2018	21/06/2018	1762	198.75	-	198.75
53					RJNL2/00002762	21/06/2018	21/06/2018	1762	618.26	-	618.26
54					RJNL2/00002759	14/06/2018	14/06/2018	1769	398.71	-	398.71
55					RJNL2/00002768	13/09/2018	13/09/2018	1678	1,534.71	-	1,534.71
56					RJNL2/00002767	16/08/2018	16/08/2018	1706	2,466.84	-	2,466.84
57					RJNL2/00002752	24/05/2018	24/05/2018	1790	2,096.37	-	2,096.37
58					RJNL2/00002766	09/08/2018	09/08/2018	1713	1,935.54	-	1,935.54
59					RJNL2/00002750	17/05/2018	17/05/2018	1797	1,897.63	-	1,897.63
60	J	-	IMD	5,000.00	RJNL2/00002649	05/11/2018	05/11/2018	1625	- 2,500.00	-	- 2,500.00
61					RJNL2/00002650	12/11/2018	12/11/2018	1618	7,500.00	-	7,500.00
Sheet9

Cell Formulas
Range	Formula
J33	J33	=-F7

 

[JohnBecks]

Hi,

Was wondering if someone was able to take a look at the above?

Thanks