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