Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to exclude a list of number from another list?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to exclude a list of number from another list?
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 "Herbert Chan" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to exclude a list of number from another list?
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 "Herbert Chan" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to exclude a list of number from another list?
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 "Herbert Chan" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to exclude a list of number from another list?
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 "Herbert Chan" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to exclude a list of number from another list?
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," -- Regards, Tom Ogilvy "AA2e72E" wrote in message ... 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 "Herbert Chan" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to exclude a list of number from another list?
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 "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 "Herbert Chan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I Exclude a list of dates in a date calculation? | Excel Worksheet Functions | |||
filtering a list to exclude 2 different dates | Excel Worksheet Functions | |||
Exclude cells that are the same as list in Excel 03 | Excel Discussion (Misc queries) | |||
list 1 has 400 names List 2 has 4000. find manes from list 1 on 2 | Excel Worksheet Functions | |||
HOW DO I TOTAL A FILTERED LIST TO EXCLUDE THOSE EXCLUDED BY FILTE. | Excel Worksheet Functions |