Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 " |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare and delete in excel | Excel Worksheet Functions | |||
Compare & Delete | Excel Discussion (Misc queries) | |||
Compare and delete cell? | Excel Programming | |||
Compare and delete or return value? | Excel Programming | |||
Excel VBA - Help with a loop, compare, delete problem | Excel Programming |