CODEWITHSUNDEEP
vbaexcel
Sean Reyes
Sean Reyes

Reputation: 1643

Compare 3 Columns in Excel using VBA

I want to compare 2 to 1 Columns in Excel using VBA..

I already achieve 2 to 2 Columns using this code 

Sub LIST1_LIST2()
    Dim list1 As Range
    Dim LIST2 As Range
    Set list1 = Range("B3:C181")
    Set LIST2 = Range("G3:H729")

    For Each row1 In list1.Rows
        For Each row2 In LIST2.Rows
          If (row1.Cells(1) = row2.Cells(1) And row1.Cells(2) = row2.Cells(2)) Then
              row1.Cells(1).Offset(0, 2) = row1.Cells(1)
              row1.Cells(2).Offset(0, 2) = row1.Cells(2)
              Exit For
          End If
        Next row2

    Next row1

End Sub

But now I need something VBA scripts that works somehow like the image below enter image description here

Upvotes: 2

Views: 1777

Answers (2)

0m3r
0m3r

Reputation: 12499

Work with a a simple Do Until Loop:

Option Explicit
Public Sub Example()
    Dim B As Range, _
        C As Range, _
        D As Range, _
        F As Range, _
        G As Range

    Dim i%, x% ' Dim as long

    Set B = Columns(2)
    Set C = Columns(3)
    Set D = Columns(4)
    Set F = Columns(6)
    Set G = Columns(7)

    i = 2
    x = 2

    Do Until IsEmpty(B.Cells(i))

        Debug.Print B.Cells(i).Value & ", " & _
                    C.Cells(i).Value ' Print on Immed Win

        Do Until IsEmpty(F.Cells(x))
            DoEvents ' For testing

            If F.Cells(x).Value = B.Cells(i).Value & ", " & _
                                  C.Cells(i).Value Then

               Debug.Print F.Cells(i).Value = B.Cells(i).Value & ", " & _
                           C.Cells(i).Value ' Print on Immed Win

                G.Cells(x) = D.Cells(i)
                x = 2 ' Reset Loop
                Exit Do
            End If

            x = x + 1
        Loop

        i = i + 1
    Loop
End Sub

Other info

DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.

Debug.Print Immediate Window is used to debug and evaluate expressions, execute statements, print variable values, and so forth. It allows you to enter expressions to be evaluated or executed by the development language during debugging. To display the Immediate window, open a project for editing, then choose Windows from the Debug menu and select Immediate, or press CTRL+ALT+I.

Upvotes: 1

JoseS
JoseS

Reputation: 26

You can use this pseudocode to guide you:

If LASTNAME = Left(NAME,LASTNAME.Length) And _
FIRSTNAME = Right(NAME,FIRSTNAME.Length) Then

Upvotes: 0

Related Questions