Compare & Delete in Excel

[smittydotbat]

I am new to the excel programming with VB and I need some help...

I have an excel workbook with one sheet. In one column I have a list o
names.
On another column I have another list of names.

What I am trying to accomplish is that when a button click event i
invoked, I would like the cells in the first column to be compare
against the other column. When it does this compare it will strip ou
the duplicates in column two (which are also in column one) and leav
what's left.

Scenario:

*Before the compare*

Column A Column B
x x
xy xyy
xyz xyz
yzx yzx

*After the Compare*

Column A Column B
x xyy
xy
xyz
yzx

If anyone can help me with this I would greatly be appreciative

 

[Dave Peterson]

How about:

Option Explicit
Sub testme01()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim res As Variant

With ActiveSheet
FirstRow = 1 'no headers in row 1???
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
res = Application.Match(.Cells(iRow, "B").Value, .Range("a:a"), 0)
If IsError(res) Then
'keep it--it's unique
Else
.Cells(res, "B").Delete shift:=xlShiftUp
End If
Next iRow
End With

End Sub

 

[nuge]

Unfortunately the code did not work. It would be great if it had, but
it gave inconsistent results. It only seems to compare adjacent cells.
It would not give the results request in example.

 

[Tom Ogilvy]

Code works perfectly for the example provided.

It would work regardless if there are duplicates in either column A or B or
both.

suspect you have altered the code to fit different conditions and have
screwed it up.

 

[nuge]

Please help me understand what I am not able to, because I really
would like this piece of code you put together to work for me. I have
copied it verbatim, but when I test it, it does not give me good
results. My test is a large series of telephone numbers in A, so I
copy them to B and insert 5 additional phone numbers in various
locations of B. In theory when I run your code should I not be left
with only the 5 new numbers I have inserted in B. Please correct me if
I did not understand. Because I do not get the 5 numbers, the only way
I will get the 5 numbers is if I do not insert new cells shifting all
the numbers in B.

 

[Dave Peterson]

Maybe you could post a little of your test data--just a small portion that
causes the error.

 

[Tom Ogilvy]

My mistake - there is a typo in Dave's code. Here is a revision:

Option Explicit
Sub testme01()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim res As Variant

With ActiveSheet
FirstRow = 1 'no headers in row 1???
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
res = Application.Match(.Cells(iRow, "B").Value, .Range("a:a"),
0)
If IsError(res) Then
'keep it--it's unique
Else
.Cells(iRow, "B").Delete shift:=xlShiftUp
End If
Next iRow
End With

End Sub


this line
.Cells(iRow, "B").Delete shift:=xlShiftUp

originally was

.Cells(res, "B").Delete shift:=xlShiftUp

--
Regards,
Tom Ogilvy



"

 

[Dave Peterson]

Ouch.

Thanks for the correction. I looked and didn't see it.

Sorry to the original poster.

 

[nuge]

Thanks!
Works like a charm!