merging data from different rows

I need to merge (actually, shift) data from different columns, but from consecutive rows, and make it into one row.

I've provided an example file with before the macro, and another tab for what I hope the macro can do for me.

Thanks!

data-merging-example.xlsx

Try this. Open workbook, enable macros and click on the button.

thanks. again!