Issue with System.Collections.ArrayList.Contains function

Hi

I am writing a script that takes data from a source and puts it into a destination data table.

The script should check if the record already exists in the destination table by checking the ID.

I have used the ArrayList.Contain functionality to do this in the past with good success. I am running into a bug where the source data seems to always be added. When I check the data I see I have duplicate records. I thought my script was not allowing for that? I can't quite see what the issue is as I used very similar code previously and it worked fine.

Here is some code. I can provide a sample file if that is helpful.

Any thoughts would be appreciated.

dim srcData as variant
srcData = Range(srcRg).Value

' create a unique list of existing records

Dim unqList As Object
Set unqList = CreateObject("System.Collections.ArrayList")


'add the existing destination data IDs as the unique list
dim i as long
i=1
For i = 1 To tbl.DataBodyRange.Rows.Count

    unqList.Add tbl.DataBodyRange(i, 1)
      ' the first column of the table data body range is the ID column
Next i


i = 1
For i = 1 To UBound(srcData, 1)


    If unqList.contains(srcData(i, 1)) = True Then
        
        'do nothing if the new record is already in the list.
        Debug.Print "This item already exists: " & srcData(i, 1)

'--------->>>>>this is where I have the issue, it seems to add the record even if it already exists in the destination table<<<<<<-------
    
    Else
          ' add the row, this part works fine.
        Set newrow = Tbl.ListRows.Add
            For j = 1 To UBound(srcData, 2)
                With newrow
                    .Range(j) = srcData(i, j)
                End With
            Next j

    End If

Next i

Hi,

Thanks for your reply and interest in helping me solve this issue.

• I am not too sure about the trailing spaces suggestion, but when I do an EXACT() comparison of the two ID ranges it does return as TRUE...
  
• I attached a sample sheet with 20 rows of dummy data. The dummy data is both in the source and the destination sheets, so I would expect that the script does not create any more rows in the destination table..
  
• Note that I have also enabled conditional formatting of duplicates in the destination range in order to highlight where duplicates exist.

Any and all help would be appreciated, as I will be managing a huge volume of this data in the next few months.

Thank you!

You should add the value to the ArrayList not the range.
Change this:

    unqList.Add tbl.DataBodyRange(i, 1)

to this:

    unqList.Add tbl.DataBodyRange(i, 1).Value

Thank you!

That was indeed the issue. I will mark the thread as solved!

You're welcome, glad to help & thanks for the feedback.