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

 

[Abdo]

You need to tell us clear logic to separate the data.

actually I haven't ,but I will think something maybe help you and comeback later.

 

[Abdo]

Well when run the macro then will write DONE word in column H for last row like this

DD‬.xlsx
	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	0.00	0.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00	DONE
DEB

Cell Formulas
Range	Formula
L3	L3	=SUM(L$2:L2)
M3:N3	M3	=SUM(M$2:M3)

and when enter new data will start after DONE word like this.

DD.xlsx
	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		2,000.00	-2,000.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00	DONE	TOTAL			40,000.00	3,000.00	37,000.00
5	05/01/2022	SA-B36	ABDEND1	SA	0.00	1,000.00	38,000.00
6	07/01/2022	SA-B37	ABDEND1	SA	0.00	1,000.00	37,000.00	DONE
DEB

Cell Formulas
Range	Formula
L4:N4	L4	=SUM(L$2:L3)

I hope this is is logical.

 

[Fuji]

Sub test()
    Dim a, i&, ii&, iii&, n&, temp
    With Sheets("deb")
        a = .[a1].CurrentRegion.Resize(, 8).Value2
        ReDim b(1 To UBound(a, 1), 1 To 6)
        .Columns("i:n").Clear
        For i = 2 To UBound(a, 1)
            If (temp <> a(i, 3)) + (a(i, 8) = "DONE") Then
                temp = a(i, 3): n = n + 1: ii = 0
                b(n, 1) = a(i, 1): b(n, 2) = a(i, 1): b(n, 3) = a(i, 3)
                Do While temp = a(i + ii, 3)
                    If b(n, 1) > a(i + ii, 1) Then b(n, 1) = a(i + ii, 1)
                    If b(n, 2) < a(i + ii, 1) Then b(n, 2) = a(i + ii, 1)
                    For iii = 1 To 2
                        b(n, iii + 3) = b(n, iii + 3) + a(i + ii, iii + 4)
                    Next
                    b(n, 6) = b(n, 4) - b(n, 5)
                    ii = ii + 1
                    If a(i + ii - 1, 8) = "DONE" Then temp = "": Exit Do
                    If (i + ii > UBound(a, 1)) Then Exit Do
                Loop
                i = i + ii - 1
            End If
        Next
        .Range("g" & Rows.Count).End(xlUp)(1, 2) = "DONE"
        .[a1].Copy .[i1:n1]
        .[i1:n1] = [{"FROM DATE","TO DATE","CLIENT NO","DEBIT","CREDIT","BALANCE"}]
        .[i2].Resize(n, 6) = b
        With [i1].CurrentRegion.Offset(1)
            .HorizontalAlignment = xlCenter
            .Columns("a:b").NumberFormatLocal = "dd/mm/yyyy"
            .Columns("d:f").NumberFormatLocal = "#,#.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
        End With
        [i1].CurrentRegion.Borders.Weight = 2
    End With
End Sub

 

[Abdo]

there is problem about formatting for TOTAL row .I notice from first data will create borders under TOTAL row !
no need make borders start from column H , should be from column I.

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	5,555.00	.00	20,000.00		01/01/2022	05/01/2022	ABDEND1	25,555.00	1,000.00	24,555.00
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	.00	40,000.00		TOTAL			25,555.00	1,000.00	24,555.00
4	05/01/2022	SA-B35	ABDEND1	SA	.00	1,000.00	39,000.00	DONE
DEB

Cell Formulas
Range	Formula
L3:N3	L3	=SUM(L$2:L2)

when enter new data

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	5,555.00	.00	20,000.00		01/01/2022	05/01/2022	ABDEND1	25,555.00	1,000.00	24,555.00
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	.00	40,000.00		TOTAL			25,555.00	1,000.00	24,555.00
4	05/01/2022	SA-B35	ABDEND1	SA	.00	1,000.00	39,000.00	DONE
5	05/01/2022	SA-B36	ABDEND1	SA		2,000.00	37,000.00
6	06/01/2022	PA-B4	ABDEND1	PA	2,000.00		39,000.00
7	07/01/2022	PA-B5	ABDEND1	PA	2,000.00		41,000.00
DEB

Cell Formulas
Range	Formula
L3:N3	L3	=SUM(L$2:L2)

then will move TOTAL row to range A:G and change date formatting in column I!

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	5,555.00	.00	20,000.00		44562	44566	ABDEND1	25555	1000	24555
3	04/01/2022	PA-B3	ABDEND1	PA	20,000.00	.00	40,000.00		44566	44568	ABDEND1	4000	2000	2000
4	05/01/2022	SA-B35	ABDEND1	SA	.00	1,000.00	39,000.00	DONE
5	05/01/2022	SA-B36	ABDEND1	SA		2,000.00	37,000.00
6	06/01/2022	PA-B4	ABDEND1	PA	2,000.00		39,000.00
7	07/01/2022	PA-B5	ABDEND1	PA	2,000.00		41,000.00	DONE
8	TOTAL			.00	29,555.00	3,000.00
DEB

Cell Formulas
Range	Formula
D8:F8	D8	=SUM(D$2:D7)

 

[Fuji]

Sub test()
    Dim a, i&, ii&, iii&, n&, temp
    With Sheets("deb")
        .Columns("i:n").Clear
        a = .[a1].CurrentRegion.Resize(, 8).Value2
        ReDim b(1 To UBound(a, 1), 1 To 6)
        For i = 2 To UBound(a, 1)
            If (temp <> a(i, 3)) + (a(i, 8) = "DONE") Then
                temp = a(i, 3): n = n + 1: ii = 0
                b(n, 1) = a(i, 1): b(n, 2) = a(i, 1): b(n, 3) = a(i, 3)
                Do While temp = a(i + ii, 3)
                    If b(n, 1) > a(i + ii, 1) Then b(n, 1) = a(i + ii, 1)
                    If b(n, 2) < a(i + ii, 1) Then b(n, 2) = a(i + ii, 1)
                    For iii = 1 To 2
                        b(n, iii + 3) = b(n, iii + 3) + a(i + ii, iii + 4)
                    Next
                    b(n, 6) = b(n, 4) - b(n, 5)
                    ii = ii + 1
                    If a(i + ii - 1, 8) = "DONE" Then temp = "": Exit Do
                    If (i + ii > UBound(a, 1)) Then Exit Do
                Loop
                i = i + ii - 1
            End If
        Next
        .Range("g" & Rows.Count).End(xlUp)(1, 2) = "DONE"
        .[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 = "#,#.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]

Awesome!
last thing, can you add condition, please? when there is zero value into last cell BALANCE column G for adjacent cell contains DONE word in column H then should not show in range I:N (Check rows 7:9) like this

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		2,000.00	-2,000.00
4	05/01/2022	SA-B35	ABDEND1	SA	0.00	1,000.00	39,000.00	DONE	TOTAL			40,000.00	3,000.00	37,000.00
5	05/01/2022	SA-B36	ABDEND1	SA	0.00	1,000.00	38,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-B37	ABDEND2	SA		6,000.00	0.00	DONE
DEB

Cell Formulas
Range	Formula
L4:N4	L4	=SUM(L$2:L3)

 

[Fuji]

Really a poor data management...

What if the block of data contains multiple CLIENT NO up to DONE?

And when you ask question, ask everything in the initial post, otherwise it need to be re-built from scratch.
So, ask when you know everything you ask.

 

[Abdo]

What if the block of data contains multiple CLIENT NO up to DONE?

not really understand it , sorry !
if you mean there are many client no contains zero in last row should ignore when start amount until last row contains zero for adjacant DONE word for new entering data.
I suppose the code will deal for new entering data after DONE word for previous data so should search for after DONE word for previous data until DONE word for new entering data and when zero in last row for new entering data then will ignore whole range for new entering data.

And when you ask question, ask everything in the initial post, otherwise it need to be re-built from scratch.
So, ask when you know everything you ask.

understood.

 

[Fuji]

What is the result for

Date	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
1/1/2022	-	ABDEND1	OPENNING	20000	0	20000
4/1/2022	PA-B3	ABDEND1	PA	20000	0	40000
5/1/2022	SA-B35	ABDEND1	SA	0	1000	39000
5/1/2022	SA-B36	ABDEND1	SA	0	1000	38000
7/1/2022	SA-B37	ABDEND1	ABDEND2	0	1000	37000
8/1/2022	PA-B4	ABDEND2	PA	3000		3000
9/1/2022	PA-B5	ABDEND2	PA	3000		6000
10/1/2022	SA-B37	ABDEND2	SA		6000	0	DONE

 

[Abdo]

OK when I entering data for specific name will not be different others names in the same entering data.
I will enter one name for each process alone.