Deleting a set of entries based on sum of entries in the set

[regn]

Hi,

I have a table that has columns date, code and amount. There are many other columns but they are irrelevant. Each line in this table belongs to a group. Each group has a code in the corresponding column. That is, each line has own date and amount, but the code is the same for the group. I would like to delete all the groups of lines if the sum of their amount equals to zero. How can that be done?

[alansidman]

I have used Power Query, called Get and Transform Data in your version of Excel and found on the Data Tab.

I loaded your table into the PQ editor. Data-->Get & Transform Data-->From Table or Range

I then applied the following Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Code", Int64.Type}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"Data", each List.Sum([Amount]), type nullable number}})
in
    #"Grouped Rows"

I then made a duplicate of the original table in PQ and created a merge of each table to the other (a left outer join)

Here is the Mcode for that next section

let
    Source = Table.NestedJoin(Table1, {"Code"}, #"Table1 (2)", {"Code"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Data] = 650)),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(#"Filtered Rows", "Table1 (2)", {"Amount"}, {"Amount"})
in
    #"Expanded Table1 (2)"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[regn]

Thanks for the hint! Yes, I thought this could be done with PQ. There are actually many ways to accoplish this, the easiest one is probably is through SUMIFS funciton. You can just insert this function in each line (with the code as criteria) and delete all the rows that return 0. I'm just learning VBA, and this is why I wonder how this can be done with more advanced code.

[jindon]

regn,
What's happen to your previous thread.
https://www.excelforum.com/excel-pro...ntire-set.html

vba

Sub test()
    Dim a, e, i As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    With Sheets("sheet1").Cells(1).CurrentRegion
        a = .Value
        For i = 2 To UBound(a, 1)
            a(i, 2) = CStr(a(i, 2))
            dic(a(i, 2)) = dic(a(i, 2)) + a(i, 3)
        Next
        For Each e In dic
            If dic(e) <> 0 Then dic.Remove e
        Next
        If dic.Count Then
            .AutoFilter 2, dic.keys, 7
            .Offset(1).EntireRow.Delete
        End If
        .Parent.AutoFilterMode = False
    End With
End Sub

[regn]

@Jindon

The requirements were a little bit different this time, so I created a new thread.

Thanks again! This works!

[alansidman]

Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding. I have removed the quotes this time.