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

 

[Rijnsent]

Hi Abdo,
what code are you looking for, VBA? If so, what start did you write yourself? For the elements in columns L & M you can use the SUMIFS formula - once you've set up the data in I,J an K.
Cheers,
Koen

 

[Abdo]

If so, what start did you write yourself?

if I have then I posted .
this project seem to be difficult to achieve that, that's why I said.

I hope finding macro

For the elements in columns L & M you can use the SUMIFS formula - once you've set up the data in I,J an K

obviously you can't give me vba , if it's so what's the formulas should be as you suggest?

 

[Fuji]

Sub test()
    Dim s$(1), i&, r As Range
    Set r = Sheets("deb").[i1]
    r.CurrentRegion.Clear
    s(0) = "Select Min(`Date`) As `FROM DATE`, Max(`Date`) As `TO DATE`, `CLIENT NO`, " & _
           "SUM(`Debit`) As `DEBIT`, SUM(`Credit`) As `CREDIT`, SUM(`CREDIT`) - SUM(`DEBIT`) " & _
           "As `BALANCE`From `DEB$` Group By `CLIENT NO`;"
    s(1) = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
            ";Extended Properties='Excel 12.0;HDR=Yes';"
    With CreateObject("ADODB.Recordset")
        .Open s(0), s(1), 3, 3, 1
        r.Parent.Range("a1:f1").Copy r
        For i = 0 To .Fields.Count - 1
            r.Cells(1, i + 1) = .Fields(i).Name
        Next
        r.Cells(2, 1).CopyFromRecordset .DataSource
    End With
    With r.CurrentRegion
        With .Cells(.Rows.Count + 1, 1)
            .Value = "TOTAL": .Interior.Color = vbYellow: .Font.Bold = True
        End With
        .Rows(.Rows.Count + 1).Range("d1:f1") = "=sum(r2c:r[-1]c)"
        .Resize(.Rows.Count + 1).Borders.Weight = 2
        .Resize(.Rows.Count + 1).HorizontalAlignment = xlCenter
    End With
End Sub

 

[Abdo]

Thanks Fuji .
I'm not sure what my bad !
it shows error in this line

        .Open s(0), s(1), 3, 3, 1<br>

it informs me about DEB if this object is existed.
I made sure the sheet name and the headers don't contain any spaces ,but still shows the error!

 

[Fuji]

Upload your workbook that gives you such error.

 

[Abdo]

ok seem to be work.
but as I said

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.

see the picture 5,6 for last example will show duplicate name ABDEND1 doesn't merge when repeat the same name for new entering data . but the code will merge, I don't this way.
every new entering data should merge alone regardless there are the same name in previous data.

 

[Fuji]

Missed last sample data.

Sub test()
    Dim a, i&, ii&, iii&, n&, temp
    With Sheets("deb")
        a = .[a1].CurrentRegion.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) 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 i + ii > UBound(a, 1) Then Exit Do
                Loop
                i = i + ii - 1
            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].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]

Great !
I have case , I'm not sure if solve that or impossible.

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		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
L3:N3	L3	=SUM(L$2:L2)

and if In enter new data for same name and same date result should be

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		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
DEB

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

 

[Fuji]

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