Comparing two columns?

  • Thread starter Thread starter redmist
  • Start date Start date
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.
 
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
 
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
 
That's great, seems to do the job perfectly.

Thanks very much for your help, has made my life much easier
 
A good point so why not
'.EntireRow.Delete
c.clearcontents

BTW. This is FAST.

--
Don Guillett
SalesAid Software
(e-mail address removed)
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...
 
Back
Top