CODEWITHSUNDEEP
vbaexcel
thiagogps
thiagogps

Reputation: 479

Selecting elements from one column missing in another column, with VBA

I have two columns, A and B, and I need to find every element on column B that is not on column A, and paste them in a third column, C. For example:

A   B
23  5
1   4
5   7
4   23 
1

Then:

C
7

Searching for solutions, people usually suggest VLOOKUP() for similar problems. However, I need something with VBA, because each of these columns is in a different Workbook. What is a good way to do this comparison?

Thank you

Upvotes: 0

Views: 234

Answers (2)

Techie
Techie

Reputation: 181

Using loops in Vba 

counter = 0
k = 2

For i = 2 To lastrowA
    For j = 2 To lastRowB
        If Sheet1.Cells(i, "A") = Sheet1.Cells(j, "B") Then
            counter = 1
        End If
    Next j

    If counter = 0 Then
        Sheet1.Cells(k, "C") = Sheet1.Cells(j, "B")
        k = k + 1
    End If
    counter = 0
Next i

Upvotes: 1

ruedi
ruedi

Reputation: 5545

You can use VLookup in VBA. Here is the syntax:

Application.WorksheetFunction.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

Otherwise you could do loops

For each elementB in columnB
   For each elementA in columnA
      If elmentA <> ElementB then
         ---Save ElementA in an Array
      End if
   next elementB
next elementA

Upvotes: 1

Related Questions