Filter country column using loops and sumup amount in vba

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question



    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):



    CountryTransaction ID (Approval Req # - NIPR #)TierBUMain BUMCCRebate AmtPartner TATAmt in USD*Partner TAT
    KRKR76REA200515_0012T1PSGPPS27R1,654.2845 74,443
    KRKR76REA200519_0008T2PSGPPS27R17,124.3933 565,105
    AUKR76REA200522_0001T1IPGPPS77R4,955.4835 173,442
    AUKR76REA200522_0036T2IPGPPS77R1,453.9623 33,441
    AUKR76REA200528_0002T1IPGPPS77R25,080.7819 476,535
    KRKR76REA200528_0003T2IPGPPS77R14,959.6119 284,233
    HKKR76REA200528_0007T2IPGPPS77R35,181.7419 668,453
    HKKR76REA200528_0010T1PSGPPS77R9,964.8633 328,840
    KRKR76REA200528_0011T2PSGPPS77R18,330.3733 604,902

    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.




    T1Target TATRebate Paid in TAT
    AU15
    NZ15
    ID35
    KR35
    SEMC35
    MY35

    Thank you so much

    Prashanth Krishna

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



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

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



    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.

  • 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