VBA - Adding unique values to existing array

Question

Suppose I have two separate columns/arrays.

Column A have 100 rows, each with 20-length string

A(1)=daskjdkjasdj

A(2)=asdsadgggggg

A(3)=dsadpoeeeeee

Column B is a column with 200 rows, some of the rows have the same values as mentioned above, I would like to add unique one. I want to add new identifier only if it is not part of column A. (I forgot to mention that for each row with identifier there are 20 other columns with data that should be also added. My apologies)

sample data

I want to add to data A rows from Data B where identifier is not in data A. As result i would get

new data a

I was doing it with simply:

for i = 1 to lastrow_column_b
    g=0
    for j=1 to ubound(column_a)
        if column_a(j)=cells(i) then g=1
        goto skip
    next
    skip:
    if g = 0 then "do something, add to column_a"
next

But i believe there is more efficient way for doing that

Read column A into a Scripting.Dictionary. Then go down column B and add items that don't already exist. The union of the 2 sets will be the .Keys. — Comintern
– Comintern, Commented Nov 20, 2018 at 22:04
This really needs representative sample data and expected results. — tigeravatar
– tigeravatar, Commented Nov 20, 2018 at 22:30

Michal · Accepted Answer · 2018-11-21 03:12:06Z

Code below should put you on the right track. The first loop adds all entries from columns A to an array. The second loop adds only new entries from column C. The output is pasted into column E. You should be able to change the references to suit your needs. Hope that helps you with your task.

Sub UpdateColumn()
    Dim objDict As Object
    Dim key As Variant,


    ' Create new collection
    Set objDict = CreateObject("System.Collections.ArrayList")

    With ThisWorkbook.ActiveSheet
        With .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
            ' Read data of column A into array
            ' Does not check for duplicates
            For Each key In .Value
                objDict.Add key
            Next
        End With

        With .Range("C2", .Range("C" & .Rows.Count).End(xlUp))
            ' Read data of column B into array, add new entries only
            For Each key In .Value
                If Not objDict.Contains(key) Then objDict.Add key
            Next
        End With

        .Range("E2").Resize(objDict.Count) = Application.Transpose(objDict.toarray())

    End With

End Sub

Collectives™ on Stack Overflow

VBA - Adding unique values to existing array

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related