Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
Rick Rothstein wrote on 2/11/2012 :
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) 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)" -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
"=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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
Rick Rothstein presented the following explanation :
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) 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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
Rick Rothstein was thinking very hard :
"=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) 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! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
GS presented the following explanation :
Rick Rothstein presented the following explanation : 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) 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! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
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) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete cells
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete cells column. Delete empty cells | Excel Worksheet Functions | |||
how can I delete cells containing text within a range of cells | Excel Discussion (Misc queries) | |||
How to delete all the blanc cells in a worksheet and shift cells l | Excel Discussion (Misc queries) | |||
macro to select cells containing specific text and delete all cells but these | Excel Programming | |||
Delete specific cells contents in a row with some locked cells in the same row | Excel Programming |