Excel - if cell in column B exist in column A then delete cell in column B

Manually what you can do is take the help of Col C

Type this formula in Cell C1

=IF(IFERROR(VLOOKUP(B1,A:A,1,0),"")="","Not Found","Found")

And simply drag it down. You may then filter on "Found" and clear the B Cells.

If you want a vba code, I can give that as well.

Sid

Hi, no way that I know of without a macro, but try this macro. It could probably be more efficient by using the Find method, but it will do the job nevertheless.

Regards,

Rob.

Sub FindValues
	For intRow = 1 To Cells(65536, "B").End(xlUp).Row
		strColB = Cells(intRow, "B").Value
		For intRow2 = 1 To Cells(65536, "A").End(xlUp).Row
			If strColB = Cells(intRow2, "A").Value Then Cells(intRow, "B").Value = ""
		Next
	Next
End Sub

Select allOpen in new window

And here is the macro...

Sub Sample()
    Dim BLastRow As Long, ALastRow As Long, i As Long
    Dim ws As Worksheet
    Dim aCell As Range
    Dim strSearch As String
    
    Set ws = Sheets("Sheet1")
    BLastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
    ALastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To BLastRow
        strSearch = ws.Range("A" & i).Value
        
        Set aCell = ws.Range("A1:A" & ALastRow).Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        If Not aCell Is Nothing Then
            ws.Range("B" & i).ClearContents
        End If
    Next i
End Sub

Select allOpen in new window

Sid

Sorry Rob.. Didn't see your post.

Sid

No problem, but your use of Find would work a whole lot faster over a large dataset.

Rob

I accepted this solution awhile ago. ??