Trying to match data from two different tables and copy and paste data to one table

[chelsea7A]

Hello, I'm trying to figure out the correct way to match data in two separate lists. The match is the five digit numbers Once a match is found, then the next column in the second list copies the data from the next column and pastes it to the column next to the match in the first list. For example;

List 1
zone cals
10027 15
10010 32
10036 35


List 2
zones Numb
10036 35
10027 15
10010 32


I would like this to be dynamic if possible because these five digit numbers change. If a match is not found, then I just want it to be ignored. No #NA.

Please advise. Your assistance will be greatly appreciated. I was using vlookup, match with no luck.

[xladept]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[chelsea7A]

Okay here is the attachment.

[Marc L]

As a beginner starter :

Sub Demo1() 
        Dim V, R&, Rg As Range 
    With Sheet1.Range("A3", Sheet1.[A2].End(xlDown)) 
            V = .Value2 
        For R = 1 To UBound(V) 
            Set Rg = Sheet2.UsedRange.Columns(1).Find(V(R, 1), .Cells(2, 1), , xlWhole) 
             If Rg Is Nothing Then V(R, 1) = "" Else V(R, 1) = Rg(1, 2).Value2 
        Next 
            Set Rg = Nothing 
           .Offset(, 1).Value2 = V 
            Sheet2.UsedRange.Copy .Parent.[C1] 
    End With 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[xladept]

And another way:

Please Login or Register  to view this content.

[chelsea7A]

Yes! That did exactly what I needed it to do. I'm still learning this stuff since the one who knows this moved on.

[chelsea7A]

Thank you for the help. I'm still new to VBA and Excel.

[Marc L]

Without any loop, just with Excel 2007+ basics (as previous demo was under 2003 version),
as a beginner starter :

Sub Demo2()
    Dim A$
        A = Sheet2.Range("A3:B" & Sheet2.UsedRange.Rows.Count).Address(External:=True)
    With Sheet1
        With .Range("B3:B" & .UsedRange.Rows.Count)
             .Formula = "=IFERROR(VLOOKUP(A3," & A & ",2,FALSE),"""")"
             .Formula = .Value2
        End With
            Sheet2.UsedRange.Copy .[C1]
    End With
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[chelsea7A]

Hello there,

Thanks for responding. Attached is the sample of what I'm trying to do.

Take the values under column I and paste it to the Zip Code associated with it where it's already listed under column A in the same position.
This changes from time to time so it has to me dynamic.


Hopefully, it's something simple that I'm overlooking.

Thanks.

[chelsea7A]

Hello again,

Thanks for the code. But for some reason I'm getting an object not found error. please see attachment of screen shot. I tried revising
but it keeps coming up.

Thanks

[Marc L]

As you can see in the VBE Project window the CodeName of the worksheet "Sheet2" is not Sheet2 but Sheet4 …

… so replace each Sheet2 by Sheet4 within the procedure.