Assume worksheet containing the data  to be reorganized is
named "HaveAPint". Put the following sub in any code module, for
example the ThisWorkbook module:

Public Sub HaveAGuinnessInSeattle()
With Worksheets("HaveAPint")
.activate
.cells(1,1).select
do until isempty(activecell.value)
.cells(activecell.row,2).value = .cells(activecell.row + 1,1).value
activecell.offset(1,0).select
activecell.entirerow.delete
loop
end with
end sub