How do I transpose values in consecutive columns so that they fall into the first column?

[zicitron]

Hi all,

I'm using Excel 2010 and I need to transpose the values found in several columns (see Sheet 1 in file attached) so that they fall in the same order in the first column (see Sheet 2 of the file)?

Is there a quick way to do this instead of the tedious Copy/Paste as I have lots of data like this that I need to re-format in that way! The usual Transpose feature, it seems, will not do the job.

example.xlsx

[rcm]

try the macro by clicking the button

[AB33]

This is a code, not a formula. There is a button on sheet1;when you press it, you would get the result in new sheet created by the code itself.

[zicitron]

Thank you AB33...works a treat! :-)

[zicitron]

rcm, thank you for helping. I clicked on the button in the file you sent but it did not rellocate the values as expected.

[AB33]

Zic,
You are welcome!