Delete data for 20000 rows when contains words in two columns

[Abdo]

Hello ,
I have about 20000 rows divided multiple ranges . what I want to exclude lastrow contains TOTAL word in column A and exclude row contains FIRST DURETION word in column D for second row , sometimes not all ranges contain FIRST DURETION word .
after that should merge duplicated amounts in column E,F based on column D and sum whole columns D,E and show total as showing in column A,B to the bottom .every time add new ID in column D so will increase ID to show total in column A,B to the bottom.

copy to bottom based on cell.xlsm
	A	B	C	D	E	F	G
1	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
2	01/01/2024	-	ABDEND1	FIRST DURETION	20,000.00		20,000.00
3	02/01/2024	PA-B3	ABDEND1	PA	20,000.00		40,000.00
4	03/01/2024	SA-B35	ABDEND1	SA		1,000.00	39,000.00
5	TOTAL				40,000.00	1,000.00	39,000.00
6
7
8	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
9	14/01/2024	PA-B352	ABDEND10	PA	140.00		140.00
10	15/01/2024	PA-B352	ABDEND10	PA	100.00		0.00
11	TOTAL				240.00	240.00	0.00
12
13
14	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
15	14/04/2024	PA-B442	ABDEND100	FIRST DURETION	1,040.00		1,040.00
16	15/04/2024	PA-B442	ABDEND100	SA		1,000.00	40.00
17	16/04/2024	PA-B442	ABDEND100	PA	2,000.00		2,040.00
18	TOTAL				3,040.00	1,000.00	2,040.00
19
20
21	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
22	30/09/2024	PA-B1342	ABDEND1000	FIRST DURETION	10,040.00		10,040.00
23	01/10/2024	PA-B1342	ABDEND1000	SA		40.00	10,000.00
24	02/10/2024	PA-B1342	ABDEND1000	SA		8,000.00	2,000.00
25	03/10/2024	PA-B1342	ABDEND1000	PA	500.00		2,500.00
26	TOTAL				10,540.00	8,040.00	2,500.00
splitting

before running macro

copy to bottom based on cell.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
RESULT1

after running

copy to bottom based on cell.xlsm
	A	B	C	D	E	F
1	ITEM	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT
2	1	PA-B3	ABDEND1	PA	20,000.00
3	2	SA-B35	ABDEND1	SA		1,000.00
4	3	PA-B352	ABDEND10	PA	240.00
5	4	PA-B442	ABDEND100	SA		1,000.00
6	5	PA-B442	ABDEND100	PA	2,000.00
7	6	PA-B1342	ABDEND1000	SA		8,040.00
8	7	PA-B1342	ABDEND1000	PA	500.00
9
10	PA TOTAL	22,740.00
11	SA TOTAL	1,040.00
RESULT1

thanks

 

[Kerryx]

Power query method

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([DATE] <> null and [DATE] <> "TOTAL" and [DATE] <> "DATE") and ([DESCRIBE] <> "FIRST DURETION")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"BALANCE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"DATE", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"DATE"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"INVOICE NO", "DESCRIBE", "CLIENT NO"}, {{"Debit1", each List.Sum([DEBIT]), type nullable number}, {"Credit1", each List.Sum([CREDIT]), type nullable number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Debit1", "Debit"}, {"Credit1", "Credit"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "INVOICE NO", "DESCRIBE", "Debit", "Credit"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Index", "ITEM"}})
in
    #"Renamed Columns1"

Book2
	A	B	C	D	E	F
1	ITEM	INVOICE NO	CLIENT NO	DESCRIBE	Debit	Credit
2	1	PA-B3	ABDEND1	PA	20000
3	2	SA-B35	ABDEND1	SA		1000
4	3	PA-B352	ABDEND10	PA	240
5	4	PA-B442	ABDEND100	SA		1000
6	5	PA-B442	ABDEND100	PA	2000
7	6	PA-B1342	ABDEND1000	SA		8040
8	7	PA-B1342	ABDEND1000	PA	500
Table1

In power query copy the table and Group for totals

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([DATE] <> null and [DATE] <> "TOTAL" and [DATE] <> "DATE") and ([DESCRIBE] <> "FIRST DURETION")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"BALANCE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"DATE", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"DATE"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"INVOICE NO", "DESCRIBE"}, {{"Debit1", each List.Sum([DEBIT]), type nullable number}, {"Credit1", each List.Sum([CREDIT]), type nullable number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Debit1", "Debit"}, {"Credit1", "Credit"}}),
    #"Grouped Rows1" = Table.Group(#"Renamed Columns", {"DESCRIBE"}, {{"PA TOTAL", each List.Sum([Debit]), type nullable number}, {"SA TOTAL", each List.Sum([Credit]), type nullable number}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows1",{"DESCRIBE"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns2", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Book2
	A	B
1	Attribute	Value
2	PA TOTAL	22740
3	SA TOTAL	10040
Table1 (2)

 

[Fuji]

Sub test()
    Dim a, i&, ii&, s$, PA#, SA#, t&
    With Sheets("splitting")
        a = .[a1].CurrentRegion.Resize(.Cells.SpecialCells(11).Row, 6)
        a(1, 1) = "DATE"
    End With
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(a, 1)
            If a(i, 4) Like "[PS]A" Then
                t = 5 + IIf(a(i, 4) = "SA", 1, 0)
                If t = 5 Then PA = PA + a(i, t) Else SA = SA + a(i, t)
                s = Join(Array(a(i, 2), a(i, 3), a(i, 4)), Chr(2))
                If Not .exists(s) Then
                    .Item(s) = .Count + 2
                    a(.Item(s), 1) = .Count
                    For ii = 2 To UBound(a, 2)
                        a(.Item(s), ii) = a(i, ii)
                    Next
                Else
                    a(.Item(s), t) = a(.Item(s), t) + a(i, t)
                End If
            End If
        Next
        i = .Count + 1
    End With
    With Sheets("result1").Columns("a:f")
        .Clear
        .HorizontalAlignment = xlCenter
        With .Resize(i)
            .Value = a: .Borders.Weight = 2
        End With
        With .Rows(i + 2).Resize(2, 2)
            .Value = Evaluate("{""PA TOTAL""," & PA & ";""SA TOTAL""," & SA & "}")
            .Borders.Weight = 2
        End With
        With Union(.Rows(1), .Rows(i + 2).Resize(2, 1))
            .Interior.Color = 1137094
            .Font.Bold = True
        End With
        .Columns.AutoFit
    End With
End Sub

 

[Abdo]

@Kerryx
thanks, I don't prefer PQ , sorry !

 

[Abdo]

@Fuji
appreciate for writing macro for me, I will test it and come back later.

 

[Abdo]

@Fuji
code is great , but I think you forgot read this

every time add new ID in column D so will increase ID to show total in column A,B to the bottom.

based on your code , you specify only two ID in column D PA,SA but in reality I have many IDs (PA,SA,BTRY, BUUUT MMY,....)
so I want dealing with many ID in column D . in OP i just gave sample.
thanks you.

 

[Fuji]

Then you must upload the data showing all the DESCRIBE in relation to Credit/Debit.

 

[Abdo]

Then you must upload the data showing all the DESCRIBE in relation to Credit/Debit.

OK maybe this helps.

fj.xlsm
	A	B	C	D	E	F	G
1	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
2	01/01/2024	-	ABDEND1	FIRST DURETION	20,000.00		20,000.00
3	02/01/2024	PA-B3	ABDEND1	PA	20,000.00		40,000.00
4	03/01/2024	SA-B35	ABDEND1	SA		1,000.00	39,000.00
5	TOTAL				40,000.00	1,000.00	39,000.00
6
7
8	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
9	14/01/2024	PA-B352	ABDEND10	PA	140.00		140.00
10	15/01/2024	PA-B352	ABDEND10	PA	100.00		0.00
11	TOTAL				240.00	240.00	0.00
12
13
14	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
15	14/04/2024	PA-B442	ABDEND100	FIRST DURETION	1,040.00		1,040.00
16	15/04/2024	PA-B442	ABDEND100	SA		1,000.00	40.00
17	16/04/2024	PA-B442	ABDEND100	PA	2,000.00		2,040.00
18	TOTAL				3,040.00	1,000.00	2,040.00
19
20
21	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
22	30/09/2024	PA-B1342	ABDEND1000	FIRST DURETION	10,040.00		10,040.00
23	01/10/2024	PA-B1342	ABDEND1000	SA		40.00	10,000.00
24	02/10/2024	PA-B1342	ABDEND1000	SA		8,000.00	2,000.00
25	03/10/2024	PA-B1342	ABDEND1000	PA	500.00		2,500.00
26	TOTAL				10,540.00	8,040.00	2,500.00
27
28
29
30	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
31	01/10/2024	PA-B13421	ABDEND1001	BTRY	1,000.00		1,000.00
32	02/10/2024	PA-B13421	ABDEND1001	BTRY	2,000.00		3,000.00
33	03/10/2024	PA-B13421	ABDEND1001	BUUUT MMY		2,000.00	1,000.00
34	04/10/2024	PA-B13421	ABDEND1001	BUUUT MMY		1,000.00	0.00
35	TOTAL				3,000.00	3,000.00	0.00
36
37
38	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
39	01/10/2024	vbgf-00	ABDEND1002	VGHJ BHGH MTYU		1,000.00	-1,000.00
40	02/10/2024	vbgf-00	ABDEND1002	VGHJ BHGH MTYU		2,000.00	-3,000.00
41	03/10/2024	vbgf-00	ABDEND1002	VFGTRR REDG MHH	2,500.00		-500.00
42	04/10/2024	vbgf-00	ABDEND1002	VFGTRR REDG MHH	15,000.00		14,500.00
43	05/10/2024	vbgf-00	ABDEND1002	BUUUT MMY		1,000.00
44	06/10/2024	vbgf-00	ABDEND1002	BUUUT MMY		1,000.00
45	TOTAL				17,500.00	5,000.00	12,500.00
46
47
48	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
49	01/10/2024	vbgf-01	ABDEND1003	BUTRT RET	1,000.00		1,000.00
50	02/10/2024	vbgf-01	ABDEND1003	BUTRT RET	1,500.00		2,500.00
51	03/10/2024	vbgf-01	ABDEND1003	VGHJ BHGH MTYU		1,000.00	1,500.00
52	TOTAL				2,500.00	1,000.00	1,500.00
splitting

fj.xlsm
	A	B	C	D	E	F
1	ITEM	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT
2	1	PA-B3	ABDEND1	PA	20,000.00
3	2	SA-B35	ABDEND1	SA		1,000.00
4	3	PA-B352	ABDEND10	PA	240.00
5	4	PA-B442	ABDEND100	SA		1,000.00
6	5	PA-B442	ABDEND100	PA	2,000.00
7	6	PA-B1342	ABDEND1000	SA		8,040.00
8	7	PA-B1342	ABDEND1000	PA	500.00
9	8	PA-B13421	ABDEND1001	BTRY	3,000.00
10	9	PA-B13421	ABDEND1001	BUUUT MMY		3,000.00
11	10	vbgf-00	ABDEND1002	VGHJ BHGH MTYU		3,000.00
12	11	vbgf-00	ABDEND1002	VFGTRR REDG MHH	17,500.00
13	12	vbgf-00	ABDEND1002	BUUUT MMY		2,000.00
14	13	vbgf-01	ABDEND1003	BUTRT RET	2,500.00
15	14	vbgf-01	ABDEND1003	VGHJ BHGH MTYU		1,000.00
16
17	PA TOTAL	22,740.00
18	SA TOTAL	1,040.00
19	BTRY TOTAL	3,000.00
20	BUUUT MMY TOTAL	5,000.00
21	VGHJ BHGH MTYU TOTAL	4,000.00
22	VFGTRR REDG MHH TOTAL	17,500.00
23	BUTRT RET TOTAL	2,500.00
result

 

[Fuji]

Sub test()
    Dim a, i&, ii&, s$, PA#, SA#, t&, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    With Sheets("splitting")
        a = .[a1].CurrentRegion.Resize(.Cells.SpecialCells(11).Row, 6)
        a(1, 1) = "DATE"
    End With
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(a, 1)
            If (a(i, 4) <> "") * (a(i, 4) <> "FIRST DURETION") * (a(i, 4) <> "DESCRIBE") Then
                dic(a(i, 4)) = dic(a(i, 4)) + a(i, 5) + a(i, 6)
                s = Join(Array(a(i, 2), a(i, 3), a(i, 4)), Chr(2))
                If Not .exists(s) Then
                    .Item(s) = .Count + 2
                    a(.Item(s), 1) = .Count
                    For ii = 2 To UBound(a, 2)
                        a(.Item(s), ii) = a(i, ii)
                    Next
                Else
                    For ii = 5 To 6
                        a(.Item(s), ii) = a(.Item(s), ii) + a(i, ii)
                        If a(.Item(s), ii) = 0 Then a(.Item(s), ii) = Empty
                    Next
                End If
            End If
        Next
        i = .Count + 1
    End With
    With Sheets("result1").Columns("a:f")
        .Clear
        .HorizontalAlignment = xlCenter
        With .Resize(i)
            .Value = a: .Borders.Weight = 2
        End With
        With .Rows(i + 2).Resize(dic.Count, 2)
            .Value = Application.Transpose(Array(dic.keys, dic.items))
            .Columns(1) = .Parent.Evaluate(.Columns(1).Address & "&"" TOTAL""")
            .Borders.Weight = 2
        End With
        With Union(.Rows(1), .Rows(i + 2).Resize(dic.Count, 1))
            .Interior.Color = 1137094
            .Font.Bold = True
        End With
        .Columns.AutoFit
    End With
End Sub

 

[Abdo]

awesome!
may you change TOTAL for each item to the bottom in location columns D,E, please?
also should show ITEM in A1 not DATE as the code does it. and show any amount in result1 sheet like #,##0.00"

copy to bottom based on cell.xlsm
	A	B	C	D	E	F
1	ITEM	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT
2	1	PA-B3	ABDEND1	PA	20,000.00
3	2	SA-B35	ABDEND1	SA		1,000.00
4	3	PA-B352	ABDEND10	PA	240.00
5	4	PA-B442	ABDEND100	SA		1,000.00
6	5	PA-B442	ABDEND100	PA	2,000.00
7	6	PA-B1342	ABDEND1000	SA		8,040.00
8	7	PA-B1342	ABDEND1000	PA	500.00
9	8	PA-B13421	ABDEND1001	BTRY	3,000.00
10	9	PA-B13421	ABDEND1001	BUUUT MMY		3,000.00
11	10	vbgf-00	ABDEND1002	VGHJ BHGH MTYU		3,000.00
12	11	vbgf-00	ABDEND1002	VFGTRR REDG MHH	17,500.00
13	12	vbgf-00	ABDEND1002	BUUUT MMY		2,000.00
14	13	vbgf-01	ABDEND1003	BUTRT RET	2,500.00
15	14	vbgf-01	ABDEND1003	VGHJ BHGH MTYU		1,000.00
16
17				PA TOTAL	22,740.00
18				SA TOTAL	10,040.00
19				BTRY TOTAL	3,000.00
20				BUUUT MMY TOTAL	5,000.00
21				VGHJ BHGH MTYU TOTAL	4,000.00
22				VFGTRR REDG MHH TOTAL	17,500.00
23				BUTRT RET TOTAL	2,500.00
RESULT1