Macro to Subtract values where Ref is the same and then delete Duplicate Ref

**Howardc1001** · 07-05-2021, 10:25 PM

I have data on sheet "Extracted data" where the Ref is in Col A and the Value is in Col B

I would like VBA code to do the following

1) Where The Ref in Col A is the same, then the values for the references that are the same are to be subtracted from those references that are the same and the net result to be inserted in the first ref that is the same for eg Ref 115 value 549.75 in Say A5 and Ref 115 value 549.35 in A8 . The net result is 0.40 and must go into cell A5
2) Once the above has been done, then the second duplicate reference must be deleted

I have attached sample data and manually did steps 1 and 2 above and have shown the results on sheet "Final Result"

It would be appreciated if someone could assist me

**maniacb** · 07-05-2021, 10:49 PM

Within the extracted data tab, the difference between the two sets of common reference numbers is zero, yet the difference in the Final result tab shows .01. If the difference is zero should it always be .01?

**Howardc1001** · 07-05-2021, 10:57 PM

My Apologies

I have updated my sample data, which I have attached

**maniacb** · 07-05-2021, 11:43 PM

Try

Sub test426U()
Dim k&, i&, r&, c As Double, s As Double, j&
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")

k = 1
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
    If Not d.exists(Cells(i, 1).Value) Then
        d.Add Cells(i, 1).Value, k
        k = k + 1
    Else
        For j = Cells(i, 1).Row To Cells(Rows.Count, 1).End(xlUp).Row
            If Cells(i, 1).Value = Cells(j, 1).Value Then
                s = Cells(j, 2).Value
                r = Cells(j, 1).Row
            End If
        Next j
        c = Cells(i, 2).Value - s
        Cells(r, 1).EntireRow.Delete
        Cells(i, 2).Value = c
    End If
Next i

End Sub

**Howardc1001** · 07-06-2021, 12:08 AM

Thanks for the help, maniacb

Your code worked on the sample uploaded in Post # 1

However, It does not work on this sample data uploaded. I have also shown what the result should look like on sheet "Final result"

Kindly test & amend your code

**maniacb** · 07-06-2021, 12:09 AM

Post #4 code has been updated. My bad.

**Howardc1001** · 07-06-2021, 12:27 AM

Many Thanks for the help.

Code works perfectly

Macro to Subtract values where Ref is the same and then delete Duplicate Ref

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to Subtract values where Ref is the same and then delete Duplicate Ref

Re: Macro to Subtract values where Ref is the same and then delete Duplicate Ref

Re: Macro to Subtract values where Ref is the same and then delete Duplicate Ref

Re: Macro to Subtract values where Ref is the same and then delete Duplicate Ref

Re: Macro to Subtract values where Ref is the same and then delete Duplicate Ref

Re: Macro to Subtract values where Ref is the same and then delete Duplicate Ref

Re: Macro to Subtract values where Ref is the same and then delete Duplicate Ref

Thread Information

Users Browsing this Thread

Similar Threads

Add values from duplicate rows and delete duplicate

Macros to delete entire duplicate row for duplicate values

Delete duplicate values from cells containing multiple values

add, delete, update and add and subtract stock values..

VBA helps needed to Sum duplicate values and delete duplicate rows

[SOLVED] Sum Duplicate values then delete duplicate rows

Need Macro to delete duplicate Values in Column

Bookmarks

Bookmarks

Posting Permissions