[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-05-05
7
Medium Priority
?
561 Views
Last Modified: 2012-08-13
In Excel,

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

can this be done in excel without VBA? otherwise happy to go wtih scripts or whatever necessary.
0
Comment
Question by:jeremyll
  • 4
  • 2
7 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35703966
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
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35703972
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

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35703978
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

Open in new window


Sid
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35703980
Sorry Rob.. Didn't see your post.

Sid
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35703983
No problem, but your use of Find would work a whole lot faster over a large dataset.

Rob
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35703999
Oops. Found an error in my code. It was clearing cells in Col A instead of B. here is the amended code. I have made it a little faster as well.

Sub Sample()
    Dim BLastRow As Long, ALastRow As Long, i As Long
    Dim ws As Worksheet
    Dim aCell As Range, RangeToClear As Range
    Dim strSearch As String
    
    Application.ScreenUpdating = False
    
    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("B" & 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
            If RangeToClear Is Nothing Then
                Set RangeToClear = ws.Range("B" & i)
            Else
                Set RangeToClear = Union(RangeToClear, ws.Range("B" & i))
            End If
        End If
    Next i
    
    RangeToClear.ClearContents
    
    Application.ScreenUpdating = True
End Sub

Open in new window


Sid
0
 

Author Closing Comment

by:jeremyll
ID: 35726164
I accepted this solution awhile ago. ??
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question