Delete duplicate value for same date and other look up value.

[rtr.shazzad]

Dear All,


I was trying to delete the duplicate values which comes from XLOOKUP formula but is same for several dates.
For example, in attached file, In column C, HCFT comes from the Xlookup on Column B.
However, the value in Column C is repetitive for the same date.
I am looking for a VBA to remove the repetitive HCFT only (not all the rows) for the same date and Vehicle number.
My objective is to remove the colored values using VBA in Column C since those are equals to same date same vehicle.
Could you please help me to sort the problem?
Thanks in advance.


Shotez

[p45cal]

try:

Sub blah()
ActiveSheet.Range("$A$1").CurrentRegion.Resize(, 3).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End Sub

[rtr.shazzad]

try:

Sub blah()
ActiveSheet.Range("$A$1").CurrentRegion.Resize(, 3).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End Sub

Hi P45cal,
Thanks for your effort. But I was looking for something else.
I mentioned that I need to remove the duplicate values in HCFT column and other cell cell and rows will remain as it is.
Hope I can briefed well.

Shotez

[p45cal]

Change the formula in C2 to:

=IF(AND(B2=B1,A2=A1),"",XLOOKUP(B2,$F$2:$F$6,$G$2:$G$6))

and copy down.

[Paul_Hossler]

This?

Capture.JPG

Option Explicit


Sub RemoveHCFT()
    Dim i As Long
    
    With Worksheets("Sheet4").Cells(1, 1).CurrentRegion
        For i = .Rows.Count To 3 Step -1
            If .Cells(i, 1).Value = .Cells(i - 1, 1).Value And _
                .Cells(i, 2).Value = .Cells(i - 1, 2).Value And _
                .Cells(i, 3).Value = .Cells(i - 1, 3).Value Then
                .Cells(i, 3).ClearContents
            End If
        Next i
    End With
End Sub

[rtr.shazzad]

Hi Paul Hoosler,
Exactly, you are on point. Thanks a lot for your effort and time.


Shotez

[Paul_Hossler]

NP

Not the most efficient approach, but probably the simplest one