PDA

View Full Version : [SOLVED] VBA macro for deleting not matching values



geomano
02-24-2017, 10:31 AM
Hi,

I try to create a macro, which will filter the data below:
18466
Into the following output:
18467
So basically the Macro will delete rows, which have same values in columns A and B, however less than 1 unique number in column C. I was able to get the closest output to desirable by using =IF(AND(A2=A3,B2=B3,C2<>C3),1,0) function (with 1 as the desirable output), however in a situation when there is a new name and ID (col B and C) the IF provides me with 0 unfortunaltely instead of 1:

18468
I was also trying a VBA Macro, but it seems to be missing something:



Sub DupsDel()

Dim i As Long

For i = 1 To Cells.SpecialCells(xlLastCell).Row

If Cells(i, 2) = Cells(i + 1, 2) And Cells(i, 3) = Cells(i + 1, 3) And Cells(i, 4) <> Cells(i + 1, 4) Then

Cells(i + 1, 2).EntireRow.Delete

End If

Next i

End Sub

I will be very obliged for any assistance with this.

mana
02-25-2017, 06:22 AM
D2:=IF(A2<>A1,1,IF(C2<>C1,1,0))

mana
02-25-2017, 06:35 AM
Option Explicit


Sub test()
Dim i As Long

For i = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
If Cells(i, 3).Value = Cells(i - 1, 3).Value Then
Rows(i).Delete
End If
End If
Next

End Sub

geomano
02-25-2017, 03:13 PM
Thank you very much mana, the macro you have provided significantly helped me with my issue.