Consulting

Results 1 to 10 of 10

Thread: Sum (column) value across multiple column criteri

  1. #1
    VBAX Regular
    Joined
    May 2007
    Posts
    37
    Location

    Sum (column) value across multiple column criteri

    Hi All, Iím having difficulty working out how to sum (column) across multiple columns (criteria) and then delete those records except for one record (contains the sum).

    SourceFile


    Classification FullName Type Date AmountLocal Country AmountRegion Region
    ABG AlphaBetaGamma White 18/01/2024 5.00 GER 6.50 AUS
    ABG AlphaBetaGamma White 18/01/2024 7.00 GER 9.00 AUS
    ABG AlphaBetaGamma Blue 18/01/2024 5.00 GER 6.50 AUS
    ABC AlphaBetaCorr Green 19/01/2024 3.50 GER 5.00 AUS

    PseudoCode
    Where multiple (same date) records exists (criteria: FullName, Date, Type) sum (criteria: AmountLocal), keep one record (where used for sum) and delete other records (used in sum) but leave existing (other non impacted) records

    As per above
    2x dupe dates per FullName per Type, sum {5.00, 7.00}
    And output I'm aiming for is


    Classification FullName Type Date AmountLocal Country AmountRegion Region
    ABG AlphaBetaGamma White 18/01/2024 12.00 GER 15.50 AUS
    ABG AlphaBetaGamma Blue 18/01/2024 5.00 GER 6.50 AUS
    ABC AlphaBetaCorr Green 19/01/2024 3.50 GER 5.00 AUS

    I've tried SUMIFS, SUMPRODUCT but cannot get it to work.

    Any and all help appreciated; thank you.

    Sub sumData()
         Dim ws As Worksheet
         Dim As lLastRow As Long
         Dim sumRange As Range, partyRange as Range, typeRange As Range, dateRange As Range
    
         Set ws = ThisWorkbook.Sheets("SourceFiles")
    
         With ws
              lLastRow = .Cells(.Row.Count, "B").End(xlUp).Row 'Define last row
              
              set partyRange = ws.Range("B2:B" & lLastRow)'Party name
              set typeRange = ws.Range("C2:C" & lLastRow)'Type name
              set dateRange = ws.Range("D2:D" & lLastRow)'Date range
              set sumRange = ws.Range("E2:E" & lLastRow)'Costs range
              
              'Find records which Name + same Date + same Type > 1 record
              'Sum the Costs into "E2:E"
              
    
              'I tried and other methods
             .Range("M2:M" & lLastRow) = WorksheetFunction.SumIfs(partyRange, typeRange, dateRange, sumRange)
              
              'Delete other duplicate rows
    
    End Sub

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    It doesn't apply to your sample data, but if you group by FullName, Date and Type, what happens when there are different: Classifications, Countries, Regions within that group? What do you want to see in those columns? If you can tell me they'll always be the same then it's easy to do either a pivot table or Power Query by grouping by everything except AmountLocal and AmountRegion :
    2024-05-27_214237.jpg
    I'll await your response before getting into detail.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular
    Joined
    May 2007
    Posts
    37
    Location
    Hi p45cal, thanks for the reply

    " what happens when there are different: Classifications, Countries, Regions within that group?" --> Classification, countries and regions will be agnostic to the outcome. So they'll stay as they are; static.

    My main challenge is how to sum amountLocal by FullName, Type & Date; then deleting the duplicate rows to leave just 1 row post summation.

    I've not used Power Query previously.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,743
    Location
    A 'no VBA required' way would be use a pivot table for the heavy lifting and analysis

    Capture.JPG

    If you wanted, you could use VBA to copy/paste the PT and format it the way you want fpr presentation

    Personally, I try to keep (1) the data seperate from (2) the analysis and seperate from the (3) presentation, instead of trying to do everything all at once
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    May 2007
    Posts
    37
    Location
    Hi Paul, thank for the reply

    I'll give it a nudge in the morning.

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    Quote Originally Posted by demetre View Post
    " what happens when there are different: Classifications, Countries, Regions within that group?" --> Classification, countries and regions will be agnostic to the outcome. So they'll stay as they are; static.
    My main challenge is how to sum amountLocal by FullName, Type & Date; then deleting the duplicate rows to leave just 1 row post summation.
    I think I've understood the problem; I'm just asking what you want in slightly different circumstances (and if such circumstances ever happen). For example here are 4 rows all with the same FullName, Type and Date:

    2024-05-28_121027.jpg

    which you'd want summarising something like:

    2024-05-28_121143.jpg

    but look carefully at the classification column of the source data, 2 rows are ABC and two rows ABG. So what do you want to appear in the classification column of the summary? It's not a case of them being static or agnostic, it's what do you want to see?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,743
    Location
    Quote Originally Posted by demetre View Post
    Hi Paul, thank for the reply

    I'll give it a nudge in the morning.
    As an EXAMPLE, the attached WB has a data sheet, a hidden PT sheet, and a display results sheet

    The macro refreshes the PT and copies and formats the result onto the display sheet

    Changes to the PT are automatically picked up (formats, subtotals, etc.) so if you wanted to change order, etc. it's IMO easier
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Regular
    Joined
    May 2007
    Posts
    37
    Location
    Quote Originally Posted by p45cal View Post
    I think I've understood the problem; I'm just asking what you want in slightly different circumstances (and if such circumstances ever happen). For example here are 4 rows all with the same FullName, Type and Date:

    2024-05-28_121027.jpg

    which you'd want summarising something like:

    2024-05-28_121143.jpg

    but look carefully at the classification column of the source data, 2 rows are ABC and two rows ABG. So what do you want to appear in the classification column of the summary? It's not a case of them being static or agnostic, it's what do you want to see?
    I missed that p45Cal; so that'll also need to be a range to filter on. Great pickup. The classification needs to be considered in the summation given there could be a difference between them.
    So it'll be a sum (AmountLocal) based on range criteria {classification, FullName, Type, Date}
    Cheers

  9. #9
    VBAX Regular
    Joined
    May 2007
    Posts
    37
    Location
    Quote Originally Posted by Paul_Hossler View Post
    As an EXAMPLE, the attached WB has a data sheet, a hidden PT sheet, and a display results sheet

    The macro refreshes the PT and copies and formats the result onto the display sheet

    Changes to the PT are automatically picked up (formats, subtotals, etc.) so if you wanted to change order, etc. it's IMO easier
    Hi Paul
    having a look at it now
    Cheers

  10. #10
    VBAX Regular
    Joined
    May 2007
    Posts
    37
    Location
    Quote Originally Posted by Paul_Hossler View Post
    As an EXAMPLE, the attached WB has a data sheet, a hidden PT sheet, and a display results sheet

    The macro refreshes the PT and copies and formats the result onto the display sheet

    Changes to the PT are automatically picked up (formats, subtotals, etc.) so if you wanted to change order, etc. it's IMO easier
    Thanks very much Paul for the assistance

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •