Delete cells

[gwc]

How can I delete the cells in Col B if their left 16 characters do not
match the cells in Col A?

a b
000000006-6 1967 000000002-2 1957 0017731
000000006-6 1968 000000002-2 2002 0235432
000000012-1 1996 000000003-3 1957 0017043
000000034-1 1991 000000003-3 2002 0235433
000000048-4 1994 000000003-3 2003 0254130
000000048-4 1995 000000004-4 2002 0235434
000000048-4 1996 000000005-5 2002 0235435
000000048-4 1997 000000005-5 2003 0254131
000000059-4 1982 000000006-6 1967 0008339
000000059-4 1983 000000006-6 1968 0006437
000000059-4 1984 000000006-6 2002 0235436
000000059-4 1990 000000008-8 1963 0017131
000000059-4 1991 000000010-9 2002 0235437
000000063-7 1993 000000012-1 1996 0149047
000000063-7 1995 000000014-3 2002 0235438
000000063-7 1996 000000015-4 2002 0235439
000000063-7 1997 000000016-5 2002 0235440
000000034-1 1991 0101141
000000040-6 1961 0003885
000000043-9 1978 0034376
000000048-4 1994 0122266
000000048-4 1995 0135082
000000048-4 1996 0149048
000000048-4 1997 0161502
000000048-4 1998 0175210
000000048-4 1999 0186927
000000048-4 2000 0197851
000000048-4 2002 0235441
000000048-4 2008 0355864
000000048-4 2009 0372791
000000048-4 2010 0385805
000000048-4 2011 0398314
000000049-5 1959 0017982
000000051-6 1958 0016884
000000059-4 1982 0049201
000000059-4 1983 0054814
000000059-4 1984 0059644
000000059-4 1990 0094340
000000059-4 1991 0102782
000000061-5 1962 0017524
000000063-7 1993 0114767
000000063-7 1995 0135083

 

[GS]

This would be the opposite to the solution you were given last month to
delete matches. In this case you simply need to modify that solution to
delete non-matches instead of matches.<g>

 

[Rick Rothstein]

This would be the opposite to the solution you were

given last month to delete matches. In this case you
simply need to modify that solution to delete
non-matches instead of matches.<g>

Without going back to try and find it, I would be willing to bet the
modification to it would not look like this...

Sub RemoveBeginningMatchesToColumnA()
Dim X As Long, LastRow As Long, UnusedColumn As Long
Const StartRow As Long = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
UnusedColumn = Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns, LookIn:=xlFormulas).Column + 1
Application.ScreenUpdating = False
With Cells(StartRow, UnusedColumn).Resize(LastRow - StartRow + 1)
.FormulaR1C1 = "=MATCH(LEFT(RC2,16),C1,0)"
On Error Resume Next
.SpecialCells(xlFormulas, xlErrors).Offset(, _
2 - UnusedColumn).Delete xlShiftUp
.Clear
End With
Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)

 

[GS]

Rick Rothstein wrote on 2/11/2012 :

Without going back to try and find it, I would be willing to bet the
modification to it would not look like this...

Sub RemoveBeginningMatchesToColumnA()
Dim X As Long, LastRow As Long, UnusedColumn As Long
Const StartRow As Long = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
UnusedColumn = Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns, LookIn:=xlFormulas).Column + 1
Application.ScreenUpdating = False
With Cells(StartRow, UnusedColumn).Resize(LastRow - StartRow + 1)
.FormulaR1C1 = "=MATCH(LEFT(RC2,16),C1,0)"
On Error Resume Next
.SpecialCells(xlFormulas, xlErrors).Offset(, _
2 - UnusedColumn).Delete xlShiftUp
.Clear
End With
Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)

You're right.., it doesn't look at all like that! Also, there may be
some ambiguity as to whether the cell contents are 'Text' or formatted.
I would think this would be 'Text', though, and so using LEFT() should
work with a hard length. Otherwise, I'd go with using...
"=MATCH(LEFT(RC2,LEN(RC2)-8),C1,0)"

 

[Rick Rothstein]

"=MATCH(LEFT(RC2,LEN(RC2)-8),C1,0)"

The OP said "if their left 16 characters do not match..." so why LEN-8
instead of 16?

Rick Rothstein (MVP - Excel)

 

[GS]

Rick Rothstein presented the following explanation :

Without going back to try and find it, I would be willing to bet the
modification to it would not look like this...

Sub RemoveBeginningMatchesToColumnA()
Dim X As Long, LastRow As Long, UnusedColumn As Long
Const StartRow As Long = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
UnusedColumn = Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns, LookIn:=xlFormulas).Column + 1
Application.ScreenUpdating = False
With Cells(StartRow, UnusedColumn).Resize(LastRow - StartRow + 1)
.FormulaR1C1 = "=MATCH(LEFT(RC2,16),C1,0)"
On Error Resume Next
.SpecialCells(xlFormulas, xlErrors).Offset(, _
2 - UnusedColumn).Delete xlShiftUp
.Clear
End With
Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)

I meant to mention that the code I refer to handles 500,000 values in
under 10 secs. After 5 mins of runing your code on the same data my
machine froze and I had to reboot to recover. I guess the reason was as
I stated in my previous reply about values being formatted rather than
text.<g>

 

[GS]

Rick Rothstein was thinking very hard :

The OP said "if their left 16 characters do not match..." so why LEN-8
instead of 16?

Rick Rothstein (MVP - Excel)

Because the data to test is 8 characters longer than 16. Like I said,
if all values are 'Text' then it doesn't matter but if the short values
were formatted with leading zeros then we have a problem as occurred
when I tried your solution on formatted values. (Ihad to adjust for
shorter length to avoid scientific notation from occurring)

Hey, if Gary's data is exactly as posted (Text) then all is good!

 

[GS]

GS presented the following explanation :

Rick Rothstein presented the following explanation :

I meant to mention that the code I refer to handles 500,000 values in under
10 secs. After 5 mins of runing your code on the same data my machine froze
and I had to reboot to recover. I guess the reason was as I stated in my
previous reply about values being formatted rather than text.<g>

This took 58 secs to process 10,000 values!

 

[Rick Rothstein]

I did a quick look and didn't see a thread which looked similar to this
one... could you give me the date and time for the first message in that
thread so I can find it. Or, alternately, post your formula as modified for
this thread. Thanks.

Rick Rothstein (MVP - Excel)

 

[GS]

Rick Rothstein has brought this to us :

I did a quick look and didn't see a thread which looked similar to this
one... could you give me the date and time for the first message in that
thread so I can find it. Or, alternately, post your formula as modified for
this thread. Thanks.

Rick Rothstein (MVP - Excel)

I didn't do a solution for this thread because:

I was waiting for the OP to indicate he was not able to modify the
other solution. That formula removed matches. This time Gary want to
remove non-matches.

It has a bug if there's multiple matches. If he wants all matches
preserved then I need to fix the bug. Optionally, I could rewrite it
to work both ways.

You can find it in 'microsoft.public.excel.programming' under the title
"Find matches in 2 cols using Collection vs Dictionary" post 1/17/2012.

 

[GS]

Rick,
There's a new version of utility posted in the programming NG. It now
optionally gives 4 differing results:

1. Returns a list of matches with duplicates
2. Returns a list of non-matches with duplicates
3. Returns a unique list of matches (no duplicates)
4. Returns a unique list of non-matches

You can choose where to put the returned list.

Tested on two cols x 500,000 rows of data, depending on which return
options are selected the new list generated in about 10 to 12 seconds.
This might improve if Calculation/EnableEvents/ScreenUpdating are
toggled off/on, but I doubt by much since the return list gets 'dumped'
into the worksheet in one shot. This produces a slight flicker that's
reasonably acceptible IMO