Add two cells when values in another cell match

Hello, I am trying to create a Macro to Add Two values when the values in another cell Match

Here is an example:


A B
Amount Invoice Number
$509.00 #46402418 Same #
$450.00 #46417335
$450.00 #46604515
$450.00 #46497732
($59.00) #46402418 Same #
$600.00 #46651112
$800.00 #46653760

New Row $450.00 #46402418

In this case I have two columns A with the Price and B with the invoice number. I need a Macro that when the Invoice number match in column B Both values from Column A get added. In this case $509 + ($-59) = $450.

Additionally I would need to clean up the sheet, delete both values that have been added and only leave the new Column with the total.

Thanks!

Originally Posted by modytrane

is it possible that after you delete the values that were added, same invoice number could appear again?

Yes invoice number should remain the same

Originally Posted by modytrane

And do you want results in different columns than A and B?

They need to remain in the same column. All amounts on same column, all Invoice number in the same column

Originally Posted by modytrane

It would help if you can attach a sample file with your data structure so we can write the macro into it and test it.

I will atach a Google Drive file as I am not sure how to Attach something to the forum

Here is the Excel file, Sheet 1 is exactly how I get the Data, Sheet number 2 is how it should look

Thanks for the help!

https://drive.google.com/file/d/1w0A...ew?usp=sharing

File has been attached

Hi !

Easier with a workbook attachment with a before context worksheet and an after worksheet for the expected result …

I am sorry I attached the wrong file. Here is the correct one

What should happen in the same case if the dates are different, which one to keep ?

The date should be from the first one, Thanks!

A starter demonstration to paste to the (before) worksheet module :

PHP Code: 

Sub Demo1()
        Dim L&, R&, V, W
            L = Me.UsedRange.Rows.Count
    With CreateObject("Scripting.Dictionary")
        For R = 2 To L
                V = Me.UsedRange.Rows(R).Value2
            If .Exists(V(1, 4)) Then
                W = .Item(V(1, 4))
                W(1, 3) = W(1, 3) + V(1, 3)
               .Item(V(1, 4)) = W
            Else
               .Add V(1, 4), V
            End If
        Next
        If .Count < L - 1 Then
            Me.UsedRange.Rows(2 + .Count & ":" & L).Clear
            Me.UsedRange.Rows(2).Resize(.Count).Value2 = Application.Index(.Items, 0)
        End If
           .RemoveAll
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

I might not be doing it right but i get an error msg

Compile Error

"Invalid use of Me Keyword"

Error is being detected on this line:

L = Me.UsedRange.Rows.Count

Try replacing

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

in the 4 lines in the macro where "Me." is found.

Alf

Or just following the direction just before the code …

Thank you so much Marc L. It works perfectly! I really appreciate it!