Merging duplicate rows based on criteria and summing values

Hi All,

I am quite stuck with the below.

I have four columns A-D, e.g.:

I want to merge rows for which info in both columns A and B are identical, add for those rows values in column C and retain info from column D untouched (column D is irrelevant for this problem but including it here just to be consistent with my range). So my final product will look:

I also want to retain both of my lists - the original and the merged one.

To achieve the above, I have written some VBA code:

Private Sub CommandButton1_Click()

ActiveSheet.Range("A" & i & ":D" & i).Copy
ActiveSheet.Range("F" & i & ":I" & i).Select
ActiveSheet.Paste

For Each a In Range("F1", Cells(Rows.Count, "F").End(xlUp))
        For r = 1 To Cells(Rows.Count, "F").End(xlUp).Row - a.Row
            If a = a.Offset(r, 0) And a.Offset(0, 1) = a.Offset(r, 1) Then
                a.Offset(0, 2) = a.Offset(0, 2) + a.Offset(r, 2)
                a.Offset(r, 0).EntireRow.Delete
                r = r - 1
            End If
        Next r
    Next a
End Sub

First, I want to copy my original list from columns A:D to columns F:I and then I want the operations to be performed on the F:I range only.

What my code does it is copies the columns over but still performs the merging and adding the values on both ranges (the original data and the copied data). I am therefore left with two ranges side by side, both of which are merged and I lose my original data.

I am very new to VBA and cannot see a reason why it would do that. I've tried looking through posts for solutions but nothing answered my questions so far. I apologise in advance if the code is not the best but it's my first steps.

I would be extremely grateful if you could point me in the right direction.

Hi alansidman

Thank you for your reply and apologies for getting back to you so late.

Thank you for pointing out I can achieve the result I need in Power Query, that's really useful.

Just to check - apologies if I'm missing something - will the Mcode you posted help me to modify my VBA code? I do need this task running through the Command Buttons for other people to execute without a problem.

My code does almost exactly what it is supposed to, except it takes the range as a whole spreadsheet and not just the range I need it to work on. I am unsure where my coding has gone wrong but I suspect it must be something with the way I define rows? I may be completely wrong here and that's why I decided to post here.

If I use the Mcode in my command button - would that work?

Thank you!

EDIT: Apologies, I've just realised that the reason why both tables change is that I am deleting rows in my code... I have no idea why it took me so long to realise it, must have had a complete brain melt over the weekend.

I have made the procedure slightly more complicated but separated the lists into two separate sheets so that no data is being lost.

Apologies for wasting everyone's time on such a simple answer! Thanks again