Matching data in an Excel file

[dull1234]

Hello everyone
I ran into a problem with Excel that i hope you can help me

The thing i have to do is as following:

In a spreadsheet (attached file), in Sheet 1, i have a column represents the Name, and a column represents a Company Code,
and another column represent the Number Code
In sheet 2, i have a column represents the Name Tag, a column represents a Company Code, and another column represent the Number Code

and i want to match the data in sheet 2 into sheet 1 in the following way:
if the company code AND the number code of a row in sheet 2 matches the company code and the number code of a row in sheet 1,
then i want to create a new column in sheet 1 , contains the Name Tag taken from Sheet 2 correspond to the company and number code

those that are in sheet 2 and not in sheet 1 will be filled in at LAST (i.e, there will be blank cells to the left of the column we created in sheet 1)
Those that are in sheet 1 but not in sheet 2 will be left blank

The attachment i have is just a small sample
i hope you can help me come up with a VBA code for doing this
or if there is another easier way to do this, please help me

thank you very much
appreciated it.

[TheSmileyCoder]

Add a module to your excel file, and copy paste this code:

Public Sub doLookup()
 
    Dim ms As Sheet1 'ms=MySheet
    Set ms = Sheet1
 
    Dim mls As Sheet2 'mls=MyLookupSheet
    Set mls = Sheet2
 
    Dim intI As Integer
    intI = 2
 
    Dim intJ As Integer
 
    Dim myCol As New Collection
 
 
 
    Dim intComp As Integer
    Dim intNum As Integer
 
    Do While ms.Range("C" & intI) & "" <> ""
        intComp = CInt(ms.Range("C" & intI))
        intNum = CInt(ms.Range("E" & intI))
 
        'Look for match
        intJ = 2
        Do While mls.Range("D" & intJ) & "" <> ""
            If mls.Range("D" & intJ) = intComp Then
                'Found a match on first column
 
                'Does second column also match?
                    If mls.Range("F" & intJ) = intNum Then
                        'Found a match
                        ms.Range("I" & intI) = mls.Range("A" & intJ)
                        'Add to collection of found items
                        myCol.Add (CVar(mls.Range("A" & intJ)))
                        Exit Do
 
                    Else
                        'No match, keep looking
                    End If
 
            End If
 
            intJ = intJ + 1
        Loop
 
 
        Debug.Print "Running"
 
 
        intI = intI + 1
    Loop
 
    'Now we have found all the matches, now to look for those that did not match.
    intJ = 2
 
    Dim strLook As String
    Dim myVar As Variant
    Dim bFound As Boolean
 
    'Move intI 1 step back for further processing.
    intI = intI - 1
 
    Do While mls.Range("A" & intJ) & "" <> ""
        'check for it in the collection.
        'If its in the collection, then proceed to next item, if not, then add it to collection, and to sheet1
        bFound = False
        For Each myVar In myCol
            Debug.Print myVar & "=" & CVar(mls.Range("A" & intJ))
            If myVar = CVar(mls.Range("A" & intJ)) Then
                'Found it.
                bFound = True
                Exit For
            End If
        Next
 
        If Not bFound Then
            'Add it
            intI = intI + 1
            ms.Range("I" & intI) = mls.Range("A" & intJ)
        End If
 
        intJ = intJ + 1
    Loop
 
 
 
    Set myCol = Nothing
End Sub
 
 
 
 

[TheSmileyCoder]

Might not be the "best" solution, its just something I threw together.

[nico5038]

My "Access" solution would be to link sheet1 and sheet2 to Access tables and execute an UPDATE query to update sheet1.
Check my sample database. Just place it in the folder that holds your excel workbook and relink the tables.
I've added a "Test" column in front of the "Here should the results come" to show that "Query1" after running produces the correct result.

Nic;o)

[NeoPa]

Unless the data is very large I'd use VLookup() (Using VLookUp in Excel to Link to Excel 'Tables').

This can be done entirely in the spreadsheet, without recourse to VBA code.