Find unmatched strings between 2 arrays vba

Question

I'm trying to get unmatched strings in 2 arrays. I've tried this How to search for string in an array , but they return true or false.

dim arr1 as variant
dim arr2 as variant

arr1 = "abc,def,ghi,jkl"
arr2 = "abc,def,ghi,jkl,pre,ec,vw"

But i'm trying to get the strings like result="pre,ec,vw" How to specify that in vba

Those are strings, not arrays. Did you mean `arr1 = Array("abc","def,"ghi","jkl")` etc ? — Tim Williams, Sep 02 '16 at 18:07
@sam are you looking to find unmatched strings and the result is a string ? (if that's the case you don't need to insert these string into arrays) . Or are you trying to compare String elements inside 2 arrays, and the result should be the unique array elements ? — Shai Rado, Sep 03 '16 at 04:01
Have you considered using SQL to implement the same? Just a suggestion — Adarsh Madrecha, Sep 03 '16 at 05:47

h2so4 · Accepted Answer · 2016-09-03T11:04:55.673

0

try this

result=replace(arr2,arr1,"")

to list substrings that are in arr2 but not in arr1

Sub aargh()
    arr1 = "abc,def,ghi,jkl"
    arr2 = "abc,def,ghi,zer,jkl,pre,ec,vw"
    a = Split(arr1, ",")
    For i = LBound(a) To UBound(a)
        arr2 = Replace(arr2, a(i), "")
    Next i
    ol = 0
    While Len(arr2) <> ol
        ol = Len(arr2)
        arr2 = Replace(arr2, ",,", ",")
    Wend
    If Left(arr2, 1) = "," Then arr2 = Mid(arr2, 2)
    MsgBox arr2
End Sub

edited Sep 03 '16 at 11:04

answered Sep 02 '16 at 17:24

h2so4

1,559
1
10
11

I think the second parameter should be arr1 in the replace function. Also, I don't think this accounts for the leading `,`. – Ryan Wildry Sep 02 '16 at 20:36
Initial message was posted with arr and arr2 and has been edited after my answer. – h2so4 Sep 02 '16 at 21:03
Ahh, that would explain it. – Ryan Wildry Sep 02 '16 at 21:04

Find unmatched strings between 2 arrays vba

1 Answers1