Col A has 360,000 cells.
Col B has 240,000 cells.

A B
0000000021957 0000000022002
0000000022002 0000000032002
0000000031957 0000000032003
0000000032002 0000000042002
0000000032003 0000000052002
0000000042002 0000000052003
0000000052002 0000000062002
0000000052003 0000000102002
0000000061967 0000000121996
0000000061968 0000000142002
0000000062002 0000000152002
0000000081963 0000000162002
0000000102002 0000000481994
0000000121996 0000000481995
0000000142002 0000000481996
0000000152002 0000000481997
0000000162002 0000000481998
0000000341991 0000000481999
0000000401961 0000000482000

How can I delete the cells in Col A whose contents match cells in Col
B?

One method.

Insert a column left of Column A
Insert a row at top
Add titles in A1:C1..........will need for filtering


In A2 enter =COUNTIF($C$2:$C$20,B2)<>0

D-click on fill handle to copy down to bottom of Column B

Select Columns A and B only

Data>Filter>Autofilter.

Filter for True on Column A

Select from A2 to bottom of Column B.

F5>Special>Visible Cells>OK

Edit>Clear>Contents

Remove Filter.

Select A and B then F5>Special>Blanks>OK

Edit>Delete>Shift Cells Up

Done


Gord

On Sat, 14 Jan 2012 09:26:02 -0800 (PST), gary <(E-Mail Removed)>
wrote:

>Col A has 360,000 cells.
>Col B has 240,000 cells.
>
> A B
>0000000021957 0000000022002
>0000000022002 0000000032002
>0000000031957 0000000032003
>0000000032002 0000000042002
>0000000032003 0000000052002
>0000000042002 0000000052003
>0000000052002 0000000062002
>0000000052003 0000000102002
>0000000061967 0000000121996
>0000000061968 0000000142002
>0000000062002 0000000152002
>0000000081963 0000000162002
>0000000102002 0000000481994
>0000000121996 0000000481995
>0000000142002 0000000481996
>0000000152002 0000000481997
>0000000162002 0000000481998
>0000000341991 0000000481999
>0000000401961 0000000482000
>
>How can I delete the cells in Col A whose contents match cells in Col
>B?

You posted in the Programming group so I guess VBA is in order.

Sub test()
For Each cell In Range("A1:A360000")
If WorksheetFunction.CountIf(Range("B1:B240000"), _
cell.Value) <> 0 Then
cell.ClearContents
End If
Next
End Sub

You can get rid of the blanks after running.


Gord



On Sat, 14 Jan 2012 11:03:53 -0800, Gord Dibben <(E-Mail Removed)>
wrote:

>One method.
>
>Insert a column left of Column A
>Insert a row at top
>Add titles in A1:C1..........will need for filtering
>
>
>In A2 enter =COUNTIF($C$2:$C$20,B2)<>0
>
>D-click on fill handle to copy down to bottom of Column B
>
>Select Columns A and B only
>
>Data>Filter>Autofilter.
>
>Filter for True on Column A
>
>Select from A2 to bottom of Column B.
>
>F5>Special>Visible Cells>OK
>
>Edit>Clear>Contents
>
>Remove Filter.
>
>Select A and B then F5>Special>Blanks>OK
>
>Edit>Delete>Shift Cells Up
>
>Done
>
>
>Gord
>
>On Sat, 14 Jan 2012 09:26:02 -0800 (PST), gary <(E-Mail Removed)>
>wrote:
>
>>Col A has 360,000 cells.
>>Col B has 240,000 cells.
>>
>> A B
>>0000000021957 0000000022002
>>0000000022002 0000000032002
>>0000000031957 0000000032003
>>0000000032002 0000000042002
>>0000000032003 0000000052002
>>0000000042002 0000000052003
>>0000000052002 0000000062002
>>0000000052003 0000000102002
>>0000000061967 0000000121996
>>0000000061968 0000000142002
>>0000000062002 0000000152002
>>0000000081963 0000000162002
>>0000000102002 0000000481994
>>0000000121996 0000000481995
>>0000000142002 0000000481996
>>0000000152002 0000000481997
>>0000000162002 0000000481998
>>0000000341991 0000000481999
>>0000000401961 0000000482000
>>
>>How can I delete the cells in Col A whose contents match cells in Col
>>B?

On Jan 14, 11:19*am, Gord Dibben <phnor...@shaw.ca> wrote:
> You posted in the Programming group so I guess VBA is in order.
>
> Sub test()
> For Each cell In Range("A1:A360000")
> If WorksheetFunction.CountIf(Range("B1:B240000"), _
> cell.Value) <> 0 *Then
> cell.ClearContents
> End If
> Next
> End Sub
>
> You can get rid of the blanks after running.
>
> Gord
>
> On Sat, 14 Jan 2012 11:03:53 -0800, Gord Dibben <phnor...@shaw.ca>
> wrote:
>
>
>
> >One method.
>
> >Insert a column left of Column A
> >Insert a row at top
> >Add titles in A1:C1..........will need for filtering
>
> >In A2 enter * =COUNTIF($C$2:$C$20,B2)<>0
>
> >D-click on fill handle to copy down to bottom of Column B
>
> >Select Columns A and B only
>
> >Data>Filter>Autofilter.
>
> >Filter for True on Column A
>
> >Select from A2 to bottom of Column B.
>
> >F5>Special>Visible Cells>OK
>
> >Edit>Clear>Contents
>
> >Remove Filter.
>
> >Select A and B then F5>Special>Blanks>OK
>
> >Edit>Delete>Shift Cells Up
>
> >Done
>
> >Gord
>
> >On Sat, 14 Jan 2012 09:26:02 -0800 (PST), gary <gcott...@hotmail.com>
> >wrote:
>
> >>Col A has 360,000 cells.
> >>Col B has 240,000 cells.
>
> >> * * * * * * A * * * * * * * * * * * * * * * * * *B
> >>0000000021957 * 0000000022002
> >>0000000022002 * 0000000032002
> >>0000000031957 * 0000000032003
> >>0000000032002 * 0000000042002
> >>0000000032003 * 0000000052002
> >>0000000042002 * 0000000052003
> >>0000000052002 * 0000000062002
> >>0000000052003 * 0000000102002
> >>0000000061967 * 0000000121996
> >>0000000061968 * 0000000142002
> >>0000000062002 * 0000000152002
> >>0000000081963 * 0000000162002
> >>0000000102002 * 0000000481994
> >>0000000121996 * 0000000481995
> >>0000000142002 * 0000000481996
> >>0000000152002 * 0000000481997
> >>0000000162002 * 0000000481998
> >>0000000341991 * 0000000481999
> >>0000000401961 * 0000000482000
>
> >>How can I delete the cells in Col A whose contents match cells in Col
> >>B?- Hide quoted text -
>
> - Show quoted text -

Hi Gord,

How long should your macro run? (It's been running for more than 2
hours). Is there any way to determine its progress?

I have no idea how long it should take but 2 hours+ sounds a little
much.

I ran it on about 100 rows which is somewhat smaller than the range
you have. Took a second.

See Ron's macro..........proably much faster than a loop.

Did you try the manual method?


Gord



On Sat, 14 Jan 2012 14:19:10 -0800 (PST), gary <(E-Mail Removed)>
wrote:

>On Jan 14, 11:19*am, Gord Dibben <phnor...@shaw.ca> wrote:
>> You posted in the Programming group so I guess VBA is in order.
>>
>> Sub test()
>> For Each cell In Range("A1:A360000")
>> If WorksheetFunction.CountIf(Range("B1:B240000"), _
>> cell.Value) <> 0 *Then
>> cell.ClearContents
>> End If
>> Next
>> End Sub
>>
>> You can get rid of the blanks after running.
>>
>> Gord
>>
>> On Sat, 14 Jan 2012 11:03:53 -0800, Gord Dibben <phnor...@shaw.ca>
>> wrote:
>>
>>
>>
>> >One method.
>>
>> >Insert a column left of Column A
>> >Insert a row at top
>> >Add titles in A1:C1..........will need for filtering
>>
>> >In A2 enter * =COUNTIF($C$2:$C$20,B2)<>0
>>
>> >D-click on fill handle to copy down to bottom of Column B
>>
>> >Select Columns A and B only
>>
>> >Data>Filter>Autofilter.
>>
>> >Filter for True on Column A
>>
>> >Select from A2 to bottom of Column B.
>>
>> >F5>Special>Visible Cells>OK
>>
>> >Edit>Clear>Contents
>>
>> >Remove Filter.
>>
>> >Select A and B then F5>Special>Blanks>OK
>>
>> >Edit>Delete>Shift Cells Up
>>
>> >Done
>>
>> >Gord
>>
>> >On Sat, 14 Jan 2012 09:26:02 -0800 (PST), gary <gcott...@hotmail.com>
>> >wrote:
>>
>> >>Col A has 360,000 cells.
>> >>Col B has 240,000 cells.
>>
>> >> * * * * * * A * * * * * * * * * * * * * * * * * *B
>> >>0000000021957 * 0000000022002
>> >>0000000022002 * 0000000032002
>> >>0000000031957 * 0000000032003
>> >>0000000032002 * 0000000042002
>> >>0000000032003 * 0000000052002
>> >>0000000042002 * 0000000052003
>> >>0000000052002 * 0000000062002
>> >>0000000052003 * 0000000102002
>> >>0000000061967 * 0000000121996
>> >>0000000061968 * 0000000142002
>> >>0000000062002 * 0000000152002
>> >>0000000081963 * 0000000162002
>> >>0000000102002 * 0000000481994
>> >>0000000121996 * 0000000481995
>> >>0000000142002 * 0000000481996
>> >>0000000152002 * 0000000481997
>> >>0000000162002 * 0000000481998
>> >>0000000341991 * 0000000481999
>> >>0000000401961 * 0000000482000
>>
>> >>How can I delete the cells in Col A whose contents match cells in Col
>> >>B?- Hide quoted text -
>>
>> - Show quoted text -
>
>Hi Gord,
>
>How long should your macro run? (It's been running for more than 2
>hours). Is there any way to determine its progress?

On Jan 14, 2:31*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Sat, 14 Jan 2012 09:26:02 -0800 (PST), gary <gcott...@hotmail.com> wrote:
> >Col A has 360,000 cells.
> >Col B has 240,000 cells.
>
> > * * * * * * A * * * * * * * * * * * * * * * * * *B
> >0000000021957 * * * 0000000022002
> >0000000022002 * * * 0000000032002
> >0000000031957 * * * 0000000032003
> >0000000032002 * * * 0000000042002
> >0000000032003 * * * 0000000052002
> >0000000042002 * * * 0000000052003
> >0000000052002 * * * 0000000062002
> >0000000052003 * * * 0000000102002
> >0000000061967 * * * 0000000121996
> >0000000061968 * * * 0000000142002
> >0000000062002 * * * 0000000152002
> >0000000081963 * * * 0000000162002
> >0000000102002 * * * 0000000481994
> >0000000121996 * * * 0000000481995
> >0000000142002 * * * 0000000481996
> >0000000152002 * * * 0000000481997
> >0000000162002 * * * 0000000481998
> >0000000341991 * * * 0000000481999
> >0000000401961 * * * 0000000482000
>
> >How can I delete the cells in Col A whose contents match cells in Col
> >B?
>
> Here's another macro, using the AdvancedFilter.
> Please do this on a copy of your data.
>
> You will need to set ws to the proper worksheet. *I used Sheet2.
>
> =============================
> Option Explicit
> Sub PruneColA()
> * * Dim ws As Worksheet
> * * Dim rColA As Range, rColB As Range
> * * Dim c As Range
> * * Dim rCrit As Range
> * * Dim i As Long
> * * Dim v As Variant
> Set ws = Worksheets("Sheet2")
> With ws
> * * Set rColA = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
> * * Set rColB = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
> * * Set rCrit = .UsedRange.Resize(rowsize:=2, columnsize:=1).Offset _
> * * * * (columnoffset:=.UsedRange.Columns.Count + 2)
> End With
>
> Application.ScreenUpdating = False
>
> rCrit(1).ClearContents
> rCrit(2) = "=countif(" & rColB.Address & "," & rColA(2).Address(False, False) & ")>0"
>
> With rColA
> * * .AdvancedFilter Action:=xlFilterInPlace, criteriarange:=rCrit
> End With
>
> rCrit.EntireColumn.Delete
>
> On Error Resume Next
> rColA.Offset(rowoffset:=1).Resize(rowsize:=rColA.Rows.Count - 1) _
> * * .SpecialCells(xlCellTypeVisible).ClearContents
> On Error GoTo 0
>
> i = 0
> ReDim v(1 To WorksheetFunction.CountA(rColA))
> For Each c In rColA
> * * c.EntireRow.RowHeight = 15
> * * If c.Value <> "" Then
> * * * * i = i + 1
> * * * * v(i) = c.Text
> * * End If
> Next c
>
> rColA.ClearContents
> Set rColA = rColA.Resize(rowsize:=UBound(v))
> rColA = WorksheetFunction.Transpose(v)
>
> Application.ScreenUpdating = True
>
> End Sub
> ===================================- Hide quoted text -
>
> - Show quoted text -

How long should your macro run?
Is there any way to determine its progress?

On Jan 14, 3:01*pm, Gord Dibben <phnor...@shaw.ca> wrote:
> I have no idea how long it should take but 2 hours+ sounds a little
> much.
>
> I ran it on about 100 rows which is somewhat smaller than the range
> you have. *Took a second.
>
> See Ron's macro..........proably much faster than a loop.
>
> Did you try the manual method?
>
> Gord
>
> On Sat, 14 Jan 2012 14:19:10 -0800 (PST), gary <gcott...@hotmail.com>
> wrote:
>
>
>
> >On Jan 14, 11:19*am, Gord Dibben <phnor...@shaw.ca> wrote:
> >> You posted in the Programming group so I guess VBA is in order.
>
> >> Sub test()
> >> For Each cell In Range("A1:A360000")
> >> If WorksheetFunction.CountIf(Range("B1:B240000"), _
> >> cell.Value) <> 0 *Then
> >> cell.ClearContents
> >> End If
> >> Next
> >> End Sub
>
> >> You can get rid of the blanks after running.
>
> >> Gord
>
> >> On Sat, 14 Jan 2012 11:03:53 -0800, Gord Dibben <phnor...@shaw.ca>
> >> wrote:
>
> >> >One method.
>
> >> >Insert a column left of Column A
> >> >Insert a row at top
> >> >Add titles in A1:C1..........will need for filtering
>
> >> >In A2 enter * =COUNTIF($C$2:$C$20,B2)<>0
>
> >> >D-click on fill handle to copy down to bottom of Column B
>
> >> >Select Columns A and B only
>
> >> >Data>Filter>Autofilter.
>
> >> >Filter for True on Column A
>
> >> >Select from A2 to bottom of Column B.
>
> >> >F5>Special>Visible Cells>OK
>
> >> >Edit>Clear>Contents
>
> >> >Remove Filter.
>
> >> >Select A and B then F5>Special>Blanks>OK
>
> >> >Edit>Delete>Shift Cells Up
>
> >> >Done
>
> >> >Gord
>
> >> >On Sat, 14 Jan 2012 09:26:02 -0800 (PST), gary <gcott...@hotmail.com>
> >> >wrote:
>
> >> >>Col A has 360,000 cells.
> >> >>Col B has 240,000 cells.
>
> >> >> * * * * * * A * * * * * * * * * * * * * * * * * *B
> >> >>0000000021957 * 0000000022002
> >> >>0000000022002 * 0000000032002
> >> >>0000000031957 * 0000000032003
> >> >>0000000032002 * 0000000042002
> >> >>0000000032003 * 0000000052002
> >> >>0000000042002 * 0000000052003
> >> >>0000000052002 * 0000000062002
> >> >>0000000052003 * 0000000102002
> >> >>0000000061967 * 0000000121996
> >> >>0000000061968 * 0000000142002
> >> >>0000000062002 * 0000000152002
> >> >>0000000081963 * 0000000162002
> >> >>0000000102002 * 0000000481994
> >> >>0000000121996 * 0000000481995
> >> >>0000000142002 * 0000000481996
> >> >>0000000152002 * 0000000481997
> >> >>0000000162002 * 0000000481998
> >> >>0000000341991 * 0000000481999
> >> >>0000000401961 * 0000000482000
>
> >> >>How can I delete the cells in Col A whose contents match cells in Col
> >> >>B?- Hide quoted text -
>
> >> - Show quoted text -
>
> >Hi Gord,
>
> >How long should your macro run? *(It's been running for more than 2
> >hours). *Is there any way to determine its progress?- Hide quoted text-
>
> - Show quoted text -

In your =Count formula, I changed $C$20 to $C$239820 (which is the
number of cells in my spreadsheet)

When copying that formula down to the bottom of Col B, it's already
taken 30 minutes and "Calculating" is at 4%..






..

On Jan 14, 2:31*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Sat, 14 Jan 2012 09:26:02 -0800 (PST), gary <gcott...@hotmail.com> wrote:
> >Col A has 360,000 cells.
> >Col B has 240,000 cells.
>
> > * * * * * * A * * * * * * * * * * * * * * * * * *B
> >0000000021957 * * * 0000000022002
> >0000000022002 * * * 0000000032002
> >0000000031957 * * * 0000000032003
> >0000000032002 * * * 0000000042002
> >0000000032003 * * * 0000000052002
> >0000000042002 * * * 0000000052003
> >0000000052002 * * * 0000000062002
> >0000000052003 * * * 0000000102002
> >0000000061967 * * * 0000000121996
> >0000000061968 * * * 0000000142002
> >0000000062002 * * * 0000000152002
> >0000000081963 * * * 0000000162002
> >0000000102002 * * * 0000000481994
> >0000000121996 * * * 0000000481995
> >0000000142002 * * * 0000000481996
> >0000000152002 * * * 0000000481997
> >0000000162002 * * * 0000000481998
> >0000000341991 * * * 0000000481999
> >0000000401961 * * * 0000000482000
>
> >How can I delete the cells in Col A whose contents match cells in Col
> >B?
>
> Here's another macro, using the AdvancedFilter.
> Please do this on a copy of your data.
>
> You will need to set ws to the proper worksheet. *I used Sheet2.
>
> =============================
> Option Explicit
> Sub PruneColA()
> * * Dim ws As Worksheet
> * * Dim rColA As Range, rColB As Range
> * * Dim c As Range
> * * Dim rCrit As Range
> * * Dim i As Long
> * * Dim v As Variant
> Set ws = Worksheets("Sheet2")
> With ws
> * * Set rColA = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
> * * Set rColB = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
> * * Set rCrit = .UsedRange.Resize(rowsize:=2, columnsize:=1).Offset _
> * * * * (columnoffset:=.UsedRange.Columns.Count + 2)
> End With
>
> Application.ScreenUpdating = False
>
> rCrit(1).ClearContents
> rCrit(2) = "=countif(" & rColB.Address & "," & rColA(2).Address(False, False) & ")>0"
>
> With rColA
> * * .AdvancedFilter Action:=xlFilterInPlace, criteriarange:=rCrit
> End With
>
> rCrit.EntireColumn.Delete
>
> On Error Resume Next
> rColA.Offset(rowoffset:=1).Resize(rowsize:=rColA.Rows.Count - 1) _
> * * .SpecialCells(xlCellTypeVisible).ClearContents
> On Error GoTo 0
>
> i = 0
> ReDim v(1 To WorksheetFunction.CountA(rColA))
> For Each c In rColA
> * * c.EntireRow.RowHeight = 15
> * * If c.Value <> "" Then
> * * * * i = i + 1
> * * * * v(i) = c.Text
> * * End If
> Next c
>
> rColA.ClearContents
> Set rColA = rColA.Resize(rowsize:=UBound(v))
> rColA = WorksheetFunction.Transpose(v)
>
> Application.ScreenUpdating = True
>
> End Sub
> ===================================- Hide quoted text -
>
> - Show quoted text -


Your macro (using the Advanced Filter) is getting Run-time Error
'1004' of "AdvancedFilter method of Range clsss failed".

Note: I've set ws = Worksheets("Sheet1")

One way...

Option Explicit

Sub StripDupes()
Dim i&, j&, lRows1&, lRows2&, lLastRow& 'as long
Dim vRng As Variant
lRows1 = Cells(Rows.Count, "A").End(xlUp).Row
lRows2 = Cells(Rows.Count, "B").End(xlUp).Row
If lRows1 > lRows2 Then lLastRow = lRows1 Else lLastRow = lRows2
vRng = Range("A1:B" & lLastRow)
For i = UBound(vRng) To 1 Step -1
If Not vRng(i, 1) = "" Then
For j = 1 To UBound(vRng)
If vRng(i, 1) = vRng(j, 2) Then
Cells(i, 1).Delete shift:=xlUp: Exit For
End If
Next 'j
End If
Next 'i
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

You can speed it up by turning ScreenUpdating off...

> One way...
>
> Option Explicit
>
> Sub StripDupes()
> Dim i&, j&, lRows1&, lRows2&, lLastRow& 'as long
> Dim vRng As Variant
> lRows1 = Cells(Rows.Count, "A").End(xlUp).Row
> lRows2 = Cells(Rows.Count, "B").End(xlUp).Row
> If lRows1 > lRows2 Then lLastRow = lRows1 Else lLastRow = lRows2
> vRng = Range("A1:B" & lLastRow)
Application.ScreenUpdating = False
> For i = UBound(vRng) To 1 Step -1
> If Not vRng(i, 1) = "" Then
> For j = 1 To UBound(vRng)
> If vRng(i, 1) = vRng(j, 2) Then
> Cells(i, 1).Delete shift:=xlUp: Exit For
> End If
> Next 'j
> End If
> Next 'i
Application.ScreenUpdating = True
> End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc