There is data in Column 'A' (514 Records) that will also be in Column 'B'
(1600 Records), I want excel to compare ColA to ColB and remove from B the
duplicate information in A. The end result should Column B without the
duplicate records from A. How do I do this?

Public Sub ProcessData()
Dim LastRow As Long
Dim rng As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("C1").Resize(LastRow).Formula =
"=IF(ISNUMBER(MATCH(B1,A:A,0)),1,"""")"
.Columns(3).Value = .Columns(3).Value
Set rng =
Range("C:C").SpecialCells(xlCellTypeConstants).Off set(0, -1)
rng.Delete Shift:=xlUp
.Columns(3).ClearContents
End With

End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Lkyred19" wrote in message
...
There is data in Column 'A' (514 Records) that will also be in Column 'B'
(1600 Records), I want excel to compare ColA to ColB and remove from B the
duplicate information in A. The end result should Column B without the
duplicate records from A. How do I do this?

Hi,

Let's assume you are using Excel 2003 or earlier:

1. In cell C2 enter the formula COUNTIF(A$2:A$516,B2) copy it down the 1600
rows.
2. Choose Filter, Auto Filter
3. Open the auto filter on column C and choose Custom, Does not equal and
set the value to 0 (zero) This will hide all the rows that are not
duplicates.
4. Select the items in column B and press Delete (if you are using a
version before 2002 let us know). This will remove all the duplicates.
5. Turn off the filters.
6. Highlight column B and sort it. Clear column C.


--
Cheers,
Shane Devenshire


"Lkyred19" wrote:

There is data in Column 'A' (514 Records) that will also be in Column 'B'
(1600 Records), I want excel to compare ColA to ColB and remove from B the
duplicate information in A. The end result should Column B without the
duplicate records from A. How do I do this?

Here is a useful link to one of my favorite web sites:
http://www.cpearson.com/excel/Duplicates.aspx

If you have a chance, check this out too...
http://www.cpearson.com/excel/topic.aspx

Regards,
Ryan--

--
RyGuy


"ShaneDevenshire" wrote:

Hi,

Let's assume you are using Excel 2003 or earlier:

1. In cell C2 enter the formula COUNTIF(A$2:A$516,B2) copy it down the 1600
rows.
2. Choose Filter, Auto Filter
3. Open the auto filter on column C and choose Custom, Does not equal and
set the value to 0 (zero) This will hide all the rows that are not
duplicates.
4. Select the items in column B and press Delete (if you are using a
version before 2002 let us know). This will remove all the duplicates.
5. Turn off the filters.
6. Highlight column B and sort it. Clear column C.


--
Cheers,
Shane Devenshire


"Lkyred19" wrote:

There is data in Column 'A' (514 Records) that will also be in Column 'B'
(1600 Records), I want excel to compare ColA to ColB and remove from B the
duplicate information in A. The end result should Column B without the
duplicate records from A. How do I do this?