Delete Numbers Of one Col to another

[adeel1]

Hello All

i have 7,00,000 numbers in col A and in Col c i have about 85000 numbers i want to delete col C numbers from A!

i have below code its not working.

attached is sample (small) set of data.

Please Login or Register  to view this content.

Adeel

[Sintek]

One way perhaps...

Please Login or Register  to view this content.

[adeel1]

Hi sintek , thnx for your reply and code

i run it to my original file,i can understand its huge set of data


almost after min it shows "Not Responding" but still i wait almost 10min code still running...



Adeel

[Sintek]

This takes about 30 sec...85000 entries in Col A and 75000 entries in Col C

Please Login or Register  to view this content.

[adeel1]

i tried to upload file its 7Mb forum isn't allowing for such heavy file

if you don't mind to run below code than col A and C data will appear for your reference

Please Login or Register  to view this content.

[Marc L]

According to your attachment a VBA demonstration for starters which should be fast enough :

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 » ! ◄ ◄

[Sintek]

Check this option...?

Please Login or Register  to view this content.

[Marc L]

According to post #5 :

• The 'ccc' VBA procedure corrected in order to well create the initial values :

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)

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 » ! ◄ ◄

[adeel1]

@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

[adeel1]

ohh thnx @Marc L this is brilliant, may you post while when i was writing my above reply ,too fast this is, lot of thnx for this

[adeel1]

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

[Marc L]

Thanks for the rep' !

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 …

[Marc L]

Adeel,

check if you have the last version of my Demo2 VBA procedure within post #8 'cause I've made an optimization to avoid a 'glitch' …

[adeel1]

Many thnx for your help and reply!! you adopted outclass idea to make it faster

[adeel1]

i tested the code and verify results, it run without any error or glitch, thnx sir

[Marc L]

It was some logic glitch so be sure for future use to have the last version with the While codeline within the For loop …

[adeel1]

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

=+SUMPRODUCT(--(A:A<>""))
=+SUMPRODUCT(--(C:C<>""))

sum of both should be 700000 and it is, thnx
Adeel