How to exclude a list of number from another list?

[Herbert Chan]

Hello,

I've two lists of data in array in VBA.

How do I exclude the data in array1 from array2 so that I will be left with
an array2 that does not contain any number in array1?

I need this function in VBA.

Thanks.

Herbert

 

[Patrick Molloy]

Option Explicit
' set reference to Microsoft Scripting Runtime dll
Public Function NOT_List(list1 As Range, list2 As Range) As Variant
' Inputs: list1 full list
' list2 items to exclude from list1
' output list of items from list1 excluding anything from list2
Dim dFull As Scripting.Dictionary
Dim dExc As Scripting.Dictionary
Dim cell As Range
Dim result() As Variant ' output table
Dim index As Long ' loop counter

Set dFull = New Scripting.Dictionary
Set dExc = New Scripting.Dictionary

' load exclusions
For Each cell In list2.Cells
If Not dExc.Exists(cell.Value) Then
dExc.Add cell.Value, cell.Value
End If
Next

' now build full list, testing for exclusions
For Each cell In list1.Cells
If Not dExc.Exists(cell.Value) Then
' ignore duplicates
If Not dFull.Exists(cell.Value) Then
dFull.Add cell.Value, cell.Value
End If
End If
Next

'dFull is now our adjusted list
' so prepare result
ReDim result(0 To dFull.Count - 1)
For index = 0 To dFull.Count - 1
result(index) = dFull.Items(index)
Next
' transpose make the list vertical for dumping to a worksheet
NOT_List = WorksheetFunction.Transpose(result)

End Function

Method. Using a scripting dictionary from the MS Scripting Runtime DLL is
useful because the dictionary has an Exists method. We can use this to text
if an item is in the list if the item itself is the key too.
So we load a dictioanary object with the exclusions first, then we populate
another disctionary from the full list where the item does not exist in the
exclusions dictionary.

tested Ok

 

[Rowan]

This worked for me

'Assumes array1 and array2 are allready
'dimensioned and populated
'NewArray will hold the list of unique
'numbers

Dim TempArray() As Boolean
Dim NewArray() As Variant
Dim i As Integer
Dim j As Integer

ReDim TempArray(UBound(array2))

For i = 0 To UBound(array2)
For j = 0 To UBound(array1)
If array2(i) = array1(j) Then
TempArray(i) = True
End If
Next j
Next i

j = 0
For i = 0 To UBound(TempArray)
If Not TempArray(i) Then
ReDim Preserve NewArray(j)
NewArray(j) = array2(i)
j = j + 1
End If
Next i

Regards
Rowan

 

[arunkhemlai]

how about this:

' ==============================
Option Explicit

Private Sub RegExExclude()
' Must add reference to "Microsoft VBScript Regular Expressions 5.5"

Dim i, va, vb, sa, sb, regex

Set regex = New RegExp
va = Array(8, 2, 6, 4, 5, 9)
vb = Array(3, 5, 5, 2, 1, 4, 95, 151, 203)

For Each i In va
sa = sa + "( " + CStr(i) + " )|"
Next
sa = Left(sa, Len(sa) - 1) // can comment out?

For Each i In vb
sb = sb + " " + CStr(i) + " "
Next

regex.Pattern = sa
regex.Global = True

sb = regex.Replace(sb, "")
Set regex = Nothing

MsgBox Trim(sb)

End Sub
' ==============================

HTH -- Arun
arunkhemlai_at_yahoo.com

 

[AA2e72E]

This solution does not use any objects/references:

Sub array2Withoutarray1()
array1 = Array(31.4, 41.1, 7, 15.4, 47.2, 90.4, 34.5, 58, 29.7, 8.9)
array2 = Array(63.5, 75.3, 7, 1.8, 27.7, 34.5, 24.6, 34.7)
result = Without(array2, array1)
End Sub

Function Without(ByVal Keep As Variant, ByVal Exclude As Variant) As Variant
Keep = "," & Join(Keep, ",")
For i = LBound(Exclude) To UBound(Exclude)
Keep = Replace(Keep, "," & Exclude(i), "")
Next
If "," = Mid(Keep, 1, 1) Then Keep = Mid(Keep, 2)
Without = Split(Keep, ",")
End Function

 

[Tom Ogilvy]

Just a heads up --
Original, but even your sample data reveals the problem with your method as
written:
Keep before the replace loop
,63.5,75.3,7,1.8,27.7,34.5,24.6,34.7
Keep after the replace loop
63.55.3,1.8,27.7,24.6,34.7

so the first number kept is 63.55.3

When you replace using ",7" you screw up "63.5,75.3," to "63.55.5,"

 

[Herbert Chan]

Thank you very much for all the responses.

I've chosen the one using the scripting dictionary. It seems the easiet one
to use without any more tinkering.

Herbert