Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete matching cells
From the responses and their results, I think it'd be best to re-state
my OP: I need a list of the values in Col A that are NOT found in Col B. My spreadsheet contains: 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 0000000431978 0000000482002 0000000481994 0000000482008 0000000481995 0000000482009 0000000481996 0000000631995 0000000481997 0000000631996 0000000481998 0000000631997 0000000481999 0000000631998 0000000482000 0000000631999 0000000482002 0000000632000 0000000482008 0000000632001 0000000482009 0000000642000 0000000482010 0000000681994 0000000482011 0000000681995 0000000491959 0000000681996 0000000511958 0000000681997 0000000591982 0000000681998 0000000591983 0000000911997 0000000591984 0000000962001 0000000591990 0000000962003 0000000591991 0000001001997 0000000611962 0000001082006 0000000631993 0000001381994 0000000631995 0000001381995 0000000631996 0000002001994 0000000631997 0000002122007 0000000631998 0000002291995 0000000631999 0000002291996 0000000632000 0000002291997 0000000632001 0000002601999 0000000641984 0000002602000 0000000642000 0000002641998 0000000661957 0000002731994 0000000681994 0000003031994 0000000681995 0000003161994 0000000681996 0000003161995 0000000681997 0000003161996 0000000681998 0000003161997 0000000691959 0000003392009 0000000751990 0000003901998 0000000811961 0000004062006 0000000811991 0000004091994 0000000811992 0000004091995 0000000811993 0000004131998 0000000821959 0000004231998 0000000851958 0000004371995 0000000881990 0000004521995 0000000911997 0000004522000 0000000951959 0000004541997 0000000962001 0000004542000 0000000962003 0000004542001 0000001001997 0000005001998 0000001031957 0000005002002 0000001082006 0000005121997 0000001121970 0000005181994 0000001121973 0000005181998 0000001121974 0000005381995 0000001121975 0000005381996 0000001121976 0000005471994 0000001181960 0000005471995 0000001191952 0000005471996 0000001311961 0000005581996 0000001341959 0000005622000 0000001381994 0000005622001 0000001381995 0000005622002 0000001411981 0000005971994 0000001411982 0000006202004 0000001411983 0000006491995 0000001411991 0000006511994 0000001411992 0000006511996 0000001421956 0000006571994 0000001451982 0000006571995 0000001471982 0000006571996 0000001541990 0000007291994 0000001561957 0000007291996 0000001631971 0000007291998 0000001631972 0000007321994 0000001681959 0000007341997 0000001711991 0000007341998 0000001781953 0000007432003 0000001871955 0000007751995 0000001881952 0000007931996 0000001881955 0000008071994 0000001881959 0000008071995 0000001921986 0000008071997 0000001951958 0000008432000 0000001981958 0000008432001 0000002001954 0000008432002 0000002001955 0000008631995 0000002001994 0000008631996 0000002011957 0000008662000 0000002031958 0000008681998 0000002101957 0000008702003 0000002122007 0000008702004 0000002191957 0000008702005 0000002191963 0000008702006 0000002211979 0000008771994 0000002211980 0000008771995 0000002241960 0000008771996 0000002251958 0000008771997 0000002271976 0000008771998 0000002281981 0000008772000 0000002281982 0000008772003 0000002291982 0000008961994 0000002291983 0000008991996 0000002291984 0000008992005 0000002291985 0000008992006 0000002291986 0000009061997 0000002291987 0000009061998 0000002291993 0000009062006 0000002291995 0000009062007 0000002291996 0000009062009 0000002291997 0000009171995 0000002331955 0000009171997 0000002331961 0000009172003 0000002381990 0000009221994 0000002391960 0000009221996 0000002411958 0000009361995 0000002411967 0000009362003 0000002421958 0000009362004 0000002461981 0000009401994 0000002461982 0000009581998 0000002461983 0000009691994 0000002531986 0000009691995 0000002571990 0000009691996 0000002571991 0000009821996 0000002591977 0000009831996 0000002601963 0000009831997 0000002601999 0000009971994 0000002602000 0000009971995 0000002641998 0000009971996 0000002691990 0000009971997 0000002691991 0000009971998 0000002711961 0000009971999 0000002731990 0000009972000 0000002731992 0000009972001 0000002731993 0000009972002 0000002731994 0000009972003 0000002741956 0000009972004 0000002741990 0000009972005 0000002871977 0000009972006 0000002891956 0000010001998 0000002921961 0000010011994 0000002971956 0000010011995 0000002971991 0000010321994 0000002981959 0000010361997 0000003001989 0000010411996 0000003001990 0000010411997 0000003021990 0000010411998 0000003031994 0000010411999 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete matching cells
On Sun, 15 Jan 2012 14:28:31 -0800 (PST), gary wrote:
From the responses and their results, I think it'd be best to re-state my OP: I need a list of the values in Col A that are NOT found in Col B. Just use the same routine, but instead of clearing Col A and then writing the results back to Col A, define rDest and write the results the ============================= Option Explicit Sub SelectFromColA() 'Requires setting reference (tools/references) to ' Microsoft Scripting Runtime Dim ws As Worksheet Dim rColA As Range, rColB As Range Dim vColA As Variant, vColB As Variant Dim dColA As Dictionary, dColB As Dictionary Dim i As Long Dim d As Variant Dim rDest As Range Set dColA = New Dictionary Set dColB = New Dictionary Set ws = ActiveSheet 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 rDest = .Cells(1, 5) End With vColB = rColB vColA = rColA For i = LBound(vColB, 1) To UBound(vColB, 1) With dColB If Not .Exists(Key:=vColB(i, 1)) Then .Add Key:=vColB(i, 1), Item:=vColB(i, 1) End With Next i For i = LBound(vColA, 1) To UBound(vColA, 1) If Not dColB.Exists(Key:=vColA(i, 1)) Then With dColA If Not .Exists(Key:=vColA(i, 1)) Then .Add Key:=vColA(i, 1), Item:=vColA(i, 1) End With End If Next i ReDim vColA(1 To dColA.Count, 1 To 1) i = 0 For Each d In dColA i = i + 1 vColA(i, 1) = dColA(d) Next d rDest.EntireColumn.ClearContents rDest.EntireColumn.NumberFormat = "@" Set rDest = rDest.Resize(rowsize:=dColA.Count) rDest = vColA End Sub ========================== |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete matching cells
On Sun, 15 Jan 2012 14:28:31 -0800 (PST), gary wrote:
From the responses and their results, I think it'd be best to re-state my OP: I need a list of the values in Col A that are NOT found in Col B. My spreadsheet contains: A B 0000000021957 0000000022002 0000000022002 0000000032002 Gary, Hopefully you've got the formatting issue sorted. While you've been away, Jim, GS and I have been doing further work on this method. Here is a routine that also provides a list of unique (no duplicates) items in Column A that are not found in Column B, and it runs in 1/6 the time of my last macro. If the previous took a minute to run on your data set, I expect this one will run in about 10 seconds. Note that it does NOT require a reference to Microsoft Scripting Runtime ================================ Option Explicit Sub PruneColA2() Dim ws As Worksheet Dim rColA As Range, rColB As Range Dim vColA As Variant, vColB As Variant Dim vResults As Variant Dim cColB As Collection Dim i As Long Dim lBlanks As Long Dim v As Variant Dim rDest As Range Set cColB = New Collection Set ws = ActiveSheet 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 rDest = .Cells(1, 5) 'sets column for the results End With vColB = rColB vColA = rColA On Error Resume Next For i = LBound(vColB, 1) To UBound(vColB, 1) With cColB .Add Key:=vColB(i, 1), Item:=vColB(i, 1) End With Next i On Error GoTo 0 On Error GoTo NotUniqueItem For i = LBound(vColA, 1) To UBound(vColA, 1) cColB.Add Item:=vColA(i, 1), Key:=vColA(i, 1) Next i ReDim vResults(1 To UBound(vColA) - lBlanks, 1 To 1) i = 0 For Each v In vColA If v < "" Then i = i + 1 vResults(i, 1) = v End If Next v rDest.EntireColumn.ClearContents rDest.EntireColumn.NumberFormat = "@" Set rDest = rDest.Resize(rowsize:=UBound(vResults, 1)) rDest = vResults Exit Sub NotUniqueItem: vColA(i, 1) = "" lBlanks = lBlanks + 1 Resume Next End Sub ================================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete all cells in range matching certain values | Excel Programming | |||
Help with Matching Text Fields - Then Moving the Matching Cells Side by Side | Excel Discussion (Misc queries) | |||
DELETE ROW 3 MATCHING CRITERIA | Excel Programming | |||
delete all matching rows | Excel Discussion (Misc queries) | |||
Perform Lookup and delete non matching rows? | Excel Programming |