try this macro: (same in file attached):
Sub concatenate()
Dim nextRow As Long
With Sheets("Sheet1")
nextRow = Range("G" & Rows.Count).End(xlUp).Row + 1
Cells(nextRow, "G").Value = Cells(2, "A").Value & Cells(5, "D").Value
Cells(nextRow + 1, "G").Value = Cells(3, "A").Value & Cells(5, "D").Value
Cells(nextRow + 2, "G").Value = Cells(4, "A").Value & Cells(5, "D").Value
Cells(nextRow + 3, "G").Value = Cells(5, "A").Value & Cells(5, "D").Value
MsgBox "Mission accomplished!
"
End With
End Sub