(Solved) – Excel VBA – Compare two arrays and return strings that are in one array but not in the other


i have two 2-d string arrays which are of different sizes(diff number of rows), named NewAssigneesArray and AssigneesDatabase. Using only the first column of both arrays, i want to compare the two arrays and extract the strings that appear in AssigneesDatabase BUT NOT in NewAssigneesArray. I’ve been stuck on thisquite awhile now and dont seem to crack it. Because in my nested loop im comparing each element which doesnt seem to work.

P.S. There are overlapping strings in the both arrrays. That means if the string is in both of them, it should be ignored.

This is what i’ve got so far:

For a = LBound(NewAssigneesArray, 1) To UBound(NewAssigneesArray, 1)
    For b = LBound(AssigneesDatabase, 1) To UBound(AssigneesDatabase, 1)
        If NewAssigneesArray(a, 1) <> AssigneesDatabase(b, 1) Then
            lastrow = DBSheet.Cells(Rows.count, 1).End(xlUp).Row
            DBSheet.Cells(lastrow   1, 1) = AssigneesDatabase(b, 1)
            DBSheet.Cells(lastrow   1, 2) = AssigneesDatabase(b, 2)
            DBSheet.Cells(lastrow   1, 3) = "New Entry"
            Exit For
        End If
    Next b
Next a

Leave a Reply

Your email address will not be published. Required fields are marked *