Comparing two columns?

R

redmist

Hey everyone,

Am a bit new to all this Excel stuff so would appreciate it if someone
could help me out.

I have a spreadsheet with two columns of data (text). I want to check
every cell in column B and see if the same data appears somewhere in
column A. If so, I want to delete that entry from column B.

Does that make sense? I hope so! I have the feeling this should be
quite a simple task but sadly my skills aren't quite up to it!

Any help appreciated.

Thanks,

John.
 
D

Don Guillett

try this where selection is the range to compare and b24:b35 is the range
with rows to delete.Change to suit.

Sub FindandDelete()
For Each cel In Selection '[mynewdata]
On Error Resume Next
With Worksheets("Sheet2").Range("b24:b35")
Set c = .Find(cel, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Delete
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub
 
B

Bernie Deitrick

John,

I'm not sure you'll want to use Don's solution if the columns are on
the same sheet, since his code deletes the entire row.

Another solution is to use a helper column. For example, in cell C1,
use the formula

=Countif(A:A,B1)

and copy that down to match your data in column B. Then select columns
B and C and sort descending based on column C. Then select and delete
those cells that are grouped at the top, where the value in column C
isn't zero.

HTH,
Bernie
MS Excel MVP
 
R

redmist

That's great, seems to do the job perfectly.

Thanks very much for your help, has made my life much easier
 
D

Don Guillett

A good point so why not
'.EntireRow.Delete
c.clearcontents

BTW. This is FAST.

--
Don Guillett
SalesAid Software
[email protected]
Bernie Deitrick said:
John,

I'm not sure you'll want to use Don's solution if the columns are on
the same sheet, since his code deletes the entire row.

Another solution is to use a helper column. For example, in cell C1,
use the formula

=Countif(A:A,B1)

and copy that down to match your data in column B. Then select columns
B and C and sort descending based on column C. Then select and delete
those cells that are grouped at the top, where the value in column C
isn't zero.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Top