Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Delete List Contain Matching from Second List

  1. #1
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14

    Delete List Contain Matching from Second List

    Hi,

    What would be your approach to do this job

    Suppose you have 2 List, and you need to remove List2 contents from List1 , here is better code to accomplish this job , By using dictionary i just tried to make this process fast

    You just need to define two name ranges to use this Code,

    1st Cell of List1 = "RngRange"
    1st Cell of List2 ="MapDelete"

    Code:
    Sub ExcludeFromList()
     
        Dim objDicMap As Object
        Dim VarArrData
        Dim VarArrResult
        Dim rngCell   As Range
        Dim lngCOunt As Long  
    
        VarArrData = Intersect(Range(“rngRange”).CurrentRegion, Range(“rngRange”).CurrentRegion.Offset(1))
        Set objDicMap = CreateObject(“Scripting.Dictionary”)    ‘ Dictionary Object get Mapping
    
        ’Filling Dictionary
        For Each rngCell In Intersect(Range(“MapDelete”).CurrentRegion, Range(“MapDelete”).CurrentRegion.Offset(1))
            If Not objDicMap.exists(rngCell.Value) Then objDicMap.Add rngCell.Value, rngCell.Value
        Next rngCell
    
        ’Filling Result Array From Data List which would not Include Mapping Data
    
        For lngCOunt = LBound(VarArrData) To UBound(VarArrData)
            If Not objDicMap.exists(VarArrData(lngCOunt, 1)) Then
                If Not IsArray(VarArrResult) Then
                    ReDim VarArrResult(0 To 0)
                    VarArrResult(0) = VarArrData(lngCOunt, 1)
                Else
                    ReDim Preserve VarArrResult(UBound(VarArrResult) + 1)
                    VarArrResult(UBound(VarArrResult)) = VarArrData(lngCOunt, 1)
                End If
            End If
        Next lngCOunt
        ‘Clear Old List
        Range(“rngRange”).CurrentRegion.Offset(1).Clear
        ‘Replace with new list
        Range(“rngRange”).Offset(1).Resize(UBound(VarArrResult)).Value = Application.Transpose(VarArrResult)
        Set objDicMap = Nothing
        Set rngCell = Nothing
    
    End Sub
    Thanks for Reading

    Rajan.
    Last edited by Rajan_Verma; 10-04-2012 at 11:15 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Nice one Rajan. One slight problem would be that if the values in the list are not constants, they will all be converted to constants after the macro is run.....

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-30-2023 at 03:15 PM.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    Here is Another version, which will Return Value or Formula ,


    Note: Using Rand(), RandBetween(),Now() etc ,can give different Result.

    Code:
    Sub ExcludeFromList()
     
        Dim objDicMap As Object
        Dim VarArrData
        Dim VarArrResult
        Dim rngCell   As Range
        Dim lngCOunt As Long
    
        VarArrData = Intersect(Range("rngRange").CurrentRegion, Range("rngRange").CurrentRegion.Offset(1)).Formula
        Set objDicMap = CreateObject("Scripting.Dictionary")    ' Dictionary Object get Mapping
    
        'Filling Dictionary
        For Each rngCell In Intersect(Range("MapDelete").CurrentRegion, Range("MapDelete").CurrentRegion.Offset(1))
            If Not objDicMap.exists(rngCell.Value) Then objDicMap.Add rngCell.Value, rngCell.Value
        Next rngCell
    
        'Filling Result Array From Data List which would not Include Mapping Data
    
        For lngCOunt = LBound(VarArrData) To UBound(VarArrData)
            If Not objDicMap.exists(Evaluate(VarArrData(lngCOunt, 1))) Then
                If Not IsArray(VarArrResult) Then
                    ReDim VarArrResult(0 To 0)
                    VarArrResult(0) = VarArrData(lngCOunt, 1)
                Else
                    ReDim Preserve VarArrResult(UBound(VarArrResult) + 1)
                    VarArrResult(UBound(VarArrResult)) = VarArrData(lngCOunt, 1)
                End If
            End If
        Next lngCOunt
        'Clear Old List
        Range("rngRange").CurrentRegion.Offset(1).Clear
        'Replace with new list
        Range("rngRange").Offset(1).Resize(UBound(VarArrResult)).Value = Application.Transpose(VarArrResult)
        Set objDicMap = Nothing
        Set rngCell = Nothing
    
    End Sub

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Nice adaptation.....
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    a formula can also do this :

    =IFERROR(INDEX(List1,SMALL(IF(ISNA(MATCH(List1,Lis t2,0)),ROW(INDIRECT("1:" & ROWS(List1))),""),ROW(INDIRECT("1:"&COUNT(IF(ISNA( MATCH(List1,List2,0)),ROW(List1),""))))),1),"")

    Looking for a shorter One

    Rajan.
    Last edited by Rajan_Verma; 10-05-2012 at 04:40 PM.

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    here you go:

    list1: A10:A20
    list2: B10:B20

    Code:
    Sub tst()
        [A10:A20] = [if(countif(A10:A20,B10:B20)>0,"",A10:A20)]
    End Sub
    as an arrayformula:

    PHP Code:
    {=IF(COUNTIF(A10:A20;B10:B15)>0,"",A10:A20)} 
    Last edited by snb; 10-05-2012 at 12:48 AM.

  7. #7
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    i think your formula is not giving Correct result,

    Untitled.jpg

    is it your result ?

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Fair enough; in C1:

    PHP Code:
    =IF(COUNTIF($B$1:$B$11;A1)>0;"";A1
    autofill to C11.

  9. #9
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    Hi snb,
    your formula is nice.. but we can't have a list without blank cells.

    Rajan

  10. #10
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    but we can't have a list without blank cells.
    This doesn't make sense to me.

    this is how you started this thread:

    Suppose you have 2 List, and you need to remove List2 contents from List1
    Probably you are looking for:

    Code:
    Sub snb()
        sn = Application.Transpose(Filter([transpose(if(countif(B$1:B$3,A1:A12)=0,A1:A12))], "False", False))
        cells(1,3).resize(ubound(sn)+1)=sn
    End Sub
    Last edited by snb; 10-05-2012 at 08:48 PM.

Similar Threads

  1. Exclude Contents From List :
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 16
    Last Post: 06-08-2013, 12:29 AM
  2. Get Random List :
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 0
    Last Post: 06-06-2013, 07:53 PM
  3. Auto Unique List
    By r_know in forum Excel Help
    Replies: 8
    Last Post: 07-19-2012, 09:28 PM
  4. Create list with arrays
    By PcMax in forum Excel Help
    Replies: 2
    Last Post: 04-10-2012, 11:05 PM
  5. List Of All Files In A Folder
    By Excel Fox in forum Excel Help
    Replies: 2
    Last Post: 10-27-2011, 09:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •