Delete List Contain Matching from Second List

**Rajan_Verma** · 10-04-2012, 09:54 PM

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.

**Excel Fox** · 10-04-2012, 10:10 PM

**Rajan_Verma** · 10-04-2012, 10:26 PM

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

**Excel Fox** · 10-04-2012, 10:36 PM

Nice adaptation.....

**Rajan_Verma** · 10-04-2012, 11:03 PM

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.

**snb** · 10-05-2012, 12:45 AM

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)}

**Rajan_Verma** · 10-05-2012, 07:35 AM

i think your formula is not giving Correct result,

Untitled.jpg

is it your result ?

**snb** · 10-05-2012, 01:55 PM

Fair enough; in C1:

PHP Code:


=IF(COUNTIF($B$1:$B$11;A1)>0;"";A1)

autofill to C11.

**Rajan_Verma** · 10-05-2012, 04:41 PM

Hi snb,
your formula is nice.. but we can't have a list without blank cells.

Rajan

**snb** · 10-05-2012, 08:42 PM

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