Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
According to your attachment a VBA demonstration for starters which should be fast enough :
PHP Code:
Sub Demo1() Dim S& Application.ScreenUpdating = False With [A2].CurrentRegion .Columns(2).Formula = "=--ISNUMBER(MATCH(A2,C$2:C$" & [C1].End(xlDown).Row & ",0))" S = Application.Sum(.Columns(2)) If S Then .Resize(, 2).Sort .Columns(2), 1 .Rows(.Rows.Count - S + 1 & ":" & .Rows.Count).Clear End If .Columns(2).Clear End With Application.ScreenUpdating = True End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
The 'ccc' VBA procedure corrected in order to well create the initial values :
PHP Code:
Sub InitValues() Const L = 700000 Dim V, C#(), N&, R& V = Evaluate("ROW(1:" & L & ")+6665858580") ReDim C(1 To L \ 8, 0) For N = 1 To L Step 8: R = R + 1: C(R, 0) = V(N, 1): Next Application.ScreenUpdating = False [A1].Resize(L).Value2 = V [C1].Resize(UBound(C)).Value2 = C Application.ScreenUpdating = True End Sub
Considering both columns are ascending sorted the fastest way to remove matching numbers : (v2)
PHP Code:
Sub Demo2() Dim V, W, X, L&, R&, N& With [A1].CurrentRegion V = .Value2 W = [C1].CurrentRegion.Value2 ReDim X(1 To UBound(V), 0) L = 1 For R = 1 To UBound(V) While W(L, 1) < V(R, 1) And L < UBound(W): L = L + 1: Wend If V(R, 1) <> W(L, 1) Then N = N + 1: X(N, 0) = V(R, 1) Next .Value2 = X End With End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
@Marc L thnx for code its run for 5 sec but do nothing i posted data reference in my last reply post#5
@sintek thnx, but sorry its took about 10 min to run then i have stop it because its showing not responding again, i really appreciate your all efforts
Hi Marc L, if you don't mind what is the advantage of use value2 instead of only value although code is working with both! really appreciate your reply on this, please
First see the VBA help for both Value & Value2 properties.
As Value2 could be somewhat a bit faster as there is less interpretation from Excel side in particular when writting to cells …
i tested it throughly now, first I put 150000 nums in col c then i put 250000 then i put all 700000 nums in col C, all deleted without any error! still i will be careful for one or two days
i also cross verify it with vlookup in Col b then below formulas
Bookmarks