How do I conditionally append a row of Excel data from one sheet to another?

I don't use Excel very often, but I'm hoping there is a fairly straightforward way to get through this. I looked through a number of other solutions involving pasting data from one sheet to another, but I couldn't find anything that would allow me to (1) match a cell from one sheet to another and then (2) conditionally append or concatenate data instead of simply pasting it over.

I have an Excel document with two sheets of data. Both sheets contain a numerical ID column. I basically need to match the ID's from Sheet2 to the Sheet1 and then append the row data from Sheet2 to the matching rows from Sheet1. I would imagine it will look something like this:

If Sheet2 ColumnA Row1 == Sheet1 ColumnA RowX
  Copy Sheet2 Row1 Columns
  Paste (Append) to Sheet1 RowX (without overwriting the existing columns).

Sorry if there is a better way to form this question. I've managed to think myself in circles and now I feel like I have a confused Nigel Tufnel look on my face.

[Update: Updated to clarify cells to be copied.]

Solution

I think this is what you are trying to do?

The code is untested. I believe it should work. If you get any errors, let me know and we will take it form there...

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws1LR As Long, ws2LR As Long
    Dim i As Long, j As Long, LastCol As Long
    Dim ws1Rng As Range, aCell As Range
    Dim SearchString

    Set ws1 = Sheets("Sheet1")
    '~~> Assuming that ID is in Col A
    '~~> Get last row in Col A in Sheet1
    ws1LR = ws1.Range("A" & Rows.Count).End(xlUp).Row
    '~~> Set the Search Range
    Set ws1Rng = ws1.Range("A1:A" & ws1LR)

    Set ws2 = Sheets("Sheet2")
    '~~> Get last row in Col A in Sheet2
    ws2LR = ws2.Range("A" & Rows.Count).End(xlUp).Row

    '~~> Loop through the range in Sheet 2 to match it with the range in Sheet1
    For i = 1 To ws2LR
        SearchString = ws2.Range("A" & i).Value

        '~~> Search for the ID
        Set aCell = ws1Rng.Find(What:=SearchString, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        '~~> If found
        If Not aCell Is Nothing Then
            LastCol = ws2.Cells(i, ws2.Columns.Count).End(xlToLeft).Column

            '~~> Append values
            For j = 2 To LastCol
                ws1.Cells(aCell.Row, j).Value = ws1.Cells(aCell.Row, j).Value & " " & ws2.Cells(i, j).Value
            Next j
        End If
    Next i
End Sub

HTH

Sid