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

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

try:

Code:

---

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

---

Quote:

---

Originally Posted by p45cal

try:

Code:

---

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

Change the formula in C2 to:

Code:

---

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

---

and copy down.

This?

Attachment 29523

Code:

---

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

---

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


Shotez

NP

Not the most efficient approach, but probably the simplest one