How merging by sum DEBIT,CREDIT columns for each name

[Abdo]

Hi experts,
I hope finding macro to deal with client no to merge amounts in DEBIT , CREDIT but the merging will be for duplicates CLIENT NO within two dates when add data for every time .
so the report will be in columns I:N
in columns I,J will brings from column A and column K will brings from column C and columns LM will merge from column E ,F based on duplicates client no in column C within dates in column A .
the column N will subtract column L from column F and insert TOTAL row to sum column L,M .
example:

المصنف1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
2	01/01/2022	-	ABDEND1	OPENNING	20,000.00	0.00	20,000.00
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	0.00	40,000.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00
DEB

result

المصنف1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE		FROM DATE	TO DATE	CLIENT NO	DEBIT	CREDIT	BALANCE
2	01/01/2022	-	ABDEND1	OPENNING	20,000.00	0.00	20,000.00		01/01/2022	05/01/2022	ABDEND1	40,000.00	1,000.00	39,000.00
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	0.00	40,000.00		TOTAL			40,000.00	1,000.00	39,000.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00
DEB

Cell Formulas
Range	Formula
N2:N3	N2	=L2-M2
L3:M3	L3	=SUM(L2)

example:

المصنف1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE		FROM DATE	TO DATE	CLIENT NO	DEBIT	CREDIT	BALANCE
2	01/01/2022	-	ABDEND1	OPENNING	20,000.00	0.00	20,000.00		01/01/2022	05/01/2022	ABDEND1	40,000.00	1,000.00	39,000.00
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	0.00	40,000.00		TOTAL			40,000.00	1,000.00	39,000.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00
5	06/01/2022	-	ABDEND10	OPENNING	10,000.00	0.00	10,000.00
6	06/01/2022	PA-B4	ABDEND10	PA		2,000.00	8,000.00
7	07/01/2022	SA-B36	ABDEND10	SA	0.00	500.00	7,500.00
DEB

Cell Formulas
Range	Formula
N2:N3	N2	=L2-M2
L3:M3	L3	=SUM(L2:L2)

result

المصنف1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE		FROM DATE	TO DATE	CLIENT NO	DEBIT	CREDIT	BALANCE
2	01/01/2022	-	ABDEND1	OPENNING	20,000.00	0.00	20,000.00		01/01/2022	05/01/2022	ABDEND1	40,000.00	1,000.00	39,000.00
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	0.00	40,000.00		06/01/2022	07/01/2022	ABDEND10	10,000.00	2,500.00	7,500.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00		TOTAL			50,000.00	3,500.00	46,500.00
5	06/01/2022	-	ABDEND10	OPENNING	10,000.00	0.00	10,000.00
6	06/01/2022	PA-B4	ABDEND10	PA		2,000.00	8,000.00
7	07/01/2022	SA-B36	ABDEND10	SA	0.00	500.00	7,500.00
DEB

Cell Formulas
Range	Formula
N2:N4	N2	=L2-M2
L4:M4	L4	=SUM(L2:L3)

another example and important

المصنف1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE		FROM DATE	TO DATE	CLIENT NO	DEBIT	CREDIT	BALANCE
2	01/01/2022	-	ABDEND1	OPENNING	20,000.00	0.00	20,000.00		01/01/2022	05/01/2022	ABDEND1	40,000.00	1,000.00	39,000.00
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	0.00	40,000.00		06/01/2022	07/01/2022	ABDEND10	10,000.00	2,500.00	7,500.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00		TOTAL			50,000.00	3,500.00	46,500.00
5	06/01/2022	-	ABDEND10	OPENNING	10,000.00	0.00	10,000.00
6	06/01/2022	PA-B4	ABDEND10	PA		2,000.00	8,000.00
7	07/01/2022	SA-B36	ABDEND10	SA	0.00	500.00	7,500.00
8	09/01/2022	PA-B5	ABDEND1	PA	1,200.00	0.00	1,200.00
9	10/01/2022	PA-B6	ABDEND1	SA	1,000.00		2,200.00
10	11/01/2022	SA-B38	ABDEND1	SA	0.00	2,000.00	200.00
DEB

Cell Formulas
Range	Formula
N2:N4	N2	=L2-M2
L4:M4	L4	=SUM(L2:L3)

result

المصنف1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE		FROM DATE	TO DATE	CLIENT NO	DEBIT	CREDIT	BALANCE
2	01/01/2022	-	ABDEND1	OPENNING	20,000.00	0.00	20,000.00		01/01/2022	05/01/2022	ABDEND1	40,000.00	1,000.00	39,000.00
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	0.00	40,000.00		06/01/2022	07/01/2022	ABDEND10	10,000.00	2,500.00	7,500.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00		09/01/2022	11/01/2022	ABDEND1	2,200.00	2,000.00	200.00
5	06/01/2022	-	ABDEND10	OPENNING	10,000.00	0.00	10,000.00		TOTAL			52,200.00	5,500.00	46,700.00
6	06/01/2022	PA-B4	ABDEND10	PA		2,000.00	8,000.00
7	07/01/2022	SA-B36	ABDEND10	SA	0.00	500.00	7,500.00
8	09/01/2022	PA-B5	ABDEND1	PA	1,200.00	0.00	1,200.00
9	10/01/2022	PA-B6	ABDEND1	SA	1,000.00		2,200.00
10	11/01/2022	SA-B38	ABDEND1	SA	0.00	2,000.00	200.00
DEB

Cell Formulas
Range	Formula
N5,N2:N3	N2	=L2-M2
L5:M5	L5	=SUM(L2:L4)

as you see there is duplicates client no then should not merge for all of duplicates name, just merge within two dates alone for new process.
the project depends on add new data for every time so when add previous data don't repeat again.

I hope to get chance who can write code for me.
thanks

 

[Fuji]

As I said, it is you to decide how you want to maintain/manage the data, not me.
Ask when you ready.

 

[Abdo]

I try to explain this idea .
so as marked client and balance I expect to say what happens about balances there is no zero , right?

you write to to client no
ABDEND1,ABDEND2 . this is not in my case
see the case

DEB.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE		FROM DATE	TO DATE	CLIENT NO	DEBIT	CREDIT	BALANCE
2	01/01/2022	-	ABDEND1	OPENNING	20,000.00	0.00	20,000.00		01/01/2022	05/01/2022	ABDEND1	40,000.00	1,000.00	39,000.00
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	0.00	40,000.00		05/01/2022	07/01/2022	ABDEND1	.00	2,000.00	-2,000.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00	DONE	11/01/2022	12/01/2022	ABDEND2	4,000.00	.00	4,000.00
5	05/01/2022	SA-B36	ABDEND1	SA	0.00	1,000.00	38,000.00		TOTAL			44,000.00	3,000.00	41,000.00
6	07/01/2022	SA-B37	ABDEND1	ABDEND2	0.00	1,000.00	37,000.00	DONE
7	08/01/2022	PA-B4	ABDEND2	PA	3,000.00		3,000.00
8	09/01/2022	PA-B5	ABDEND2	PA	3,000.00		6,000.00
9	10/01/2022	SA-B38	ABDEND2	SA		6,000.00	0.00	DONE
10	11/01/2022	PA-B6	ABDEND2	PA	2,000.00		2,000.00
11	12/01/2022	PA-B6	ABDEND2	PA	2,000.00		4,000.00	DONE
12	12/01/2022	PA-B7	ABDEND3	PA	2,000.00		2,000.00
13	13/01/2022	SA-B38	ABDEND3	SA		2,000.00	0.00	DONE
DEB

Cell Formulas
Range	Formula
L5:N5	L5	=SUM(L$2:L4)

as you see every DONE word will be for just one client no , will not be more than name in one process
and any process contains zero will ignore it.
anyway if you still this is not clear for you I will mark solved for last code provide me .

 

[Fuji]

Sub test()
    Dim a, x, i&, ii&, iii&, n&
    With Sheets("deb")
        .[i:n].Clear
        With .[a1].CurrentRegion.Resize(, 8)
            x = Filter(.Parent.Evaluate(Replace("transpose(if((row(#)=1)+(#=""DONE""),row(#)))", "#", .Columns(8).Address)), False, 0)
            If UBound(x) < 1 Then Exit Sub
            a = .Value2
        End With
        ReDim b(1 To UBound(x) + 1, 1 To 6)
        For i = 0 To UBound(x) - 1
            If a(x(i + 1), 7) <> 0 Then
                n = n + 1
                b(n, 1) = a(x(i) + 1, 1): b(n, 2) = a(x(i) + 1, 1): b(n, 3) = a(x(i) + 1, 3)
                For ii = x(i) + 1 To x(i + 1)
                    If b(n, 1) > a(ii, 1) Then b(n, 1) = a(ii, 1)
                    If b(n, 2) < a(ii, 1) Then b(n, 1) = a(ii, 1)
                    For iii = 4 To 5
                        b(n, iii) = b(n, iii) + a(ii, iii + 1)
                    Next
                    b(n, 6) = b(n, 4) - b(n, 5)
                Next
            End If
        Next
        .[a1].Copy .[i1:n1]
        .[i1:n1] = [{"FROM DATE","TO DATE","CLIENT NO","DEBIT","CREDIT","BALANCE"}]
        .[i2].Resize(n, 6) = b
        With [i1].Resize(n + 2, UBound(b, 2))
            .HorizontalAlignment = xlCenter
            .Columns("a:b").NumberFormatLocal = "dd/mm/yyyy"
            .Columns("d:f").NumberFormatLocal = "#,0.00"
            With .Rows(.Rows.Count).Cells(1)
                .Value = "TOTAL"
                .Font.Bold = True
                .Interior.Color = vbYellow
                .Range("d1:f1").FormulaR1C1 = "=sum(r2c:r[-1]c)"
            End With
            .Borders.Weight = 2
        End With
    End With
End Sub

 

[Abdo]

application defined error or object defined error

 .[i2].Resize(n, 6) = b

DEB.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE		FROM DATE	TO DATE	CLIENT NO	DEBIT	CREDIT	BALANCE
2	01/01/2022	-	ABDEND1	OPENNING	20,000.00	0.00	20,000.00
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	0.00	40,000.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00
5
DEB

 

[Fuji]

Code in #23 has been edited and if there is no "DONE" in col.H, it will do nothing, so place "DONE" at the end of Col.H.

 

[Abdo]

just question , so the code can't do that itself by add DONE word as you did in previous code if add zero condition?

 

[Fuji]

Why do you need it to do by the code?

DONE is only used for the sign of break point of each data and you decide it for yourself without any rule, so the code will not be able to determine if placing DONE at the end is correct or not.

 

[Abdo]

DONE is only used for the sign of break point of each data and you decide it for yourself without any rule, so the code will not be able to determine if placing DONE at the end is correct or not.

ok is clear .
thank you so much for everything.