Filter country column using loops and sumup amount in vba

[xpost]

[/xpost]

Hello,

I'm working with excel vba macro, I was able to write some codes time to time by adapting already existing ones from the net or via macro recording.

This time I was not able to find a solution wandering through the net, I'll try to explain what I would like to achieve.

I have an excel sheet, clearly bigger, but more or less like the following (it is not a table neither a pivot table, it is just a worsheet filled with data):

On the column headers I have an auto-filter, I would like to automatically filter each country, Tier and Partner TAT and take sum of Rebate Amt. It means I want to filter AU, T1 and 35 (Partner TAT) and sumup Rebate Amt, then filter KR, T1, 45 and sumup the rebate amt.. and so forth, everything automatically just by pressing a button.

I was not able to figure out how to automatically filter for all the available criteria in three columns.

The total number of countries is around 100 (by the way they can partially change every month), everyday I will have around 130 countries but they can be different from the previous day.

Total rebate amount should be entered in below Table (this is T1 table)

Hope I explained myself decently.

Thank you so much

Prashanth Krishna

Quote from trunten

might be able to avoid multiple loops with a bit of a hack like so:

Code

Sub summarise()
    Dim dataSheet As Worksheet
    Dim summaryTable As Range
    Dim data, summary, d
    Dim i As Long, x As Long
    Dim k As String
    
    Set dataSheet = Sheets("Data")
    Set summaryTable = Sheets("Summary Table").Range("A1:C7")
    
    Set d = CreateObject("Scripting.Dictionary")
    summary = summaryTable.Value
    data = dataSheet.Range("A1").CurrentRegion.Value
    For i = 2 To UBound(summary)
        d(summary(i, 1) & "|" & summary(1, 1) & "|" & summary(i, 2)) = i
        summary(i, 3) = 0#
    Next i
    For i = 2 To UBound(data)
        k = data(i, 1) & "|" & data(i, 3) & "|" & data(i, 8)
        If d.exists(k) Then
            x = d(k)
            summary(x, 3) = summary(x, 3) + data(i, 7)
        End If
    Next i
    summaryTable.Value = summary
    Erase data
    Erase summary
    Set dataSheet = Nothing
    Set summaryTable = Nothing
    Set d = Nothing
End Sub

Display More

I've also attached an example so you can see how it may work

trunten Thanks a lot for providing the query. Here we need to sumup all the Rebates less than or equal to 15 days for AU/NZ region. It is taking only 15th day amount/rebate. It is not considering which is less than 15.

In the same way for ID/KR.. it should take less than or equal to 35 and sum all the rebates. These details to be filled in Sheet1 in F column and M column.

Please find the attached file for reference.

Quote from royUK

You've mentioned the obvious answer - convert the data to a Table and use a PivotTable.

Attach an example workbook, but code involving a Loop will be less efficient than using a PivotTable

royUK Please find the attached workbook. I need data in Sheet1 (F column and M column).

We need to sumup all the Rebates less than or equal to 15 days for AU/NZ region.

In the same way for ID/KR.. it should take less than or equal to 35 and sum all the rebates. These details to be filled in Sheet1 in F column and M column

Quote from trunten

Any better?

Thanks a lot trunten ..

trunten : I have modified small changes in the formula. I need to get value as $ 6287443.872. But I'm getting zero in Sheet1.

Country: Australia

Program Type: DPFR

Partner TAT: <=15

Remark: Blanks

Payment Status: Paid

If I apply these filters in raw data, I'm getting $ 6287443.872. If I run the code in module 4, I'm getting zero value. Can you help me here..

Thanks in advance.

Prashanth