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.
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.