If cell contains X (sheet 1), paste data in adjacent colums (sheet 2)

[kincaidjosh]

I'm have 2 sheets of data, I used VLOOKUP to compare variances from each sheet, but i need a macro or IF function to past the data next to my value.

I'd like to solve for the following:
If cell A1's value (in sheet 1) is also found in sheet 2, paste that data in adjacent columns from sheet 1 to sheet 2

Sheet 1
A B C
John Smith 34


Sheet 2
A B C
John

Requirement:
Find missing data from sheet 1's column B&C & paste to sheet 2's Row A next to the value, 'John'.

Sheet 2
A B C
John Smith 34

 

[Jeffrey Lopez]

First issues I see with this, is that there is no way to tell which "John" you select. If you don't have duplicates then not an issue and you can use something like this:

Sub DoingSomeWork()

Dim lngLstRow(1 To 2) As Long
Dim wsSheet(1 To 2) As String

wsSheet(1) = "Sheet1"
wsSheet(2) = "Sheet2"

lngLstRow(1) = Sheets(wsSheet(1)).UsedRange.Rows.Count
lngLstRow(2) = Sheets(wsSheet(2)).UsedRange.Rows.Count

For Each rngCell In Sheets(wsSheet(2)).Range("A2:A" & lngLstRow(1))
    For i = lngLstRow(1) To 2 Step -1
        Sheets(wsSheet(1)).Select
            Range("A" & i).Select
             If ActiveCell.Value = rngCell.Value Then
                rngCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value
                rngCell.Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value
            End If
    Next i
Next
             
End Sub

If you have duplicate values, you will need a way to make each row "unique" to get this to work.

Please let me know if you have any questions or run into any issues. Please be sure to back up your excel workbook before running this macro!