Compare & Delete in Excel
 

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

--
smittydotba
-----------------------------------------------------------------------
smittydotbat's Profile: http://www.excelforum.com/member.php...fo&userid=1523
View this thread: http://www.excelforum.com/showthread.php?threadid=26864

Compare & Delete in Excel
 

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




smittydotbat wrote:

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 of
names.
On another column I have another list of names.

What I am trying to accomplish is that when a button click event is
invoked, I would like the cells in the first column to be compared
against the other column. When it does this compare it will strip out
the duplicates in column two (which are also in column one) and leave
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.

--
smittydotbat
------------------------------------------------------------------------
smittydotbat's Profile: http://www.excelforum.com/member.php...o&userid=15232
View this thread: http://www.excelforum.com/showthread...hreadid=268648

--

Dave Peterson

Compare & Delete in Excel
 

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.

smittydotbat wrote in message ...
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 of
names.
On another column I have another list of names.

What I am trying to accomplish is that when a button click event is
invoked, I would like the cells in the first column to be compared
against the other column. When it does this compare it will strip out
the duplicates in column two (which are also in column one) and leave
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.

Compare & Delete in Excel
 

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.

--
Regards,
Tom Ogilvy

"nuge" wrote in message
om...
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.

smittydotbat wrote in message
...
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 of
names.
On another column I have another list of names.

What I am trying to accomplish is that when a button click event is
invoked, I would like the cells in the first column to be compared
against the other column. When it does this compare it will strip out
the duplicates in column two (which are also in column one) and leave
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.

Compare & Delete in Excel
 

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.

smittydotbat wrote in message ...
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 of
names.
On another column I have another list of names.

What I am trying to accomplish is that when a button click event is
invoked, I would like the cells in the first column to be compared
against the other column. When it does this compare it will strip out
the duplicates in column two (which are also in column one) and leave
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.

Compare & Delete in Excel
 

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



"

Compare & Delete in Excel
 

Ouch.

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

Sorry to the original poster.



Tom Ogilvy wrote:

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

Compare & Delete in Excel
 

Thanks!
Works like a charm!

"Tom Ogilvy" wrote in message ...
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