Convert matrix into column data

Hello,

Can anyone help me to convert matrix data anto column format. My data formatted as below.

Region 11/11/11 11/12/12 11/01/13 etc +50 columns

Scotland £50 £10 £5
England £150 0 £12
Ireland £30 £15 0

etc +200 rows

And would like to make into columns like:


Scotland 11/11/11 £50
Scotland 11/12/12 £10
Scotland 11/01/13 £5
England 11/11/11 £150
England 11/12/12 0
England 11/01/13 £12 .....

Is it achievable?? Help

Thank you in advance.

Hello,

Now test file attached.

Thank you.

The output in sheet2

Hi Ab33,

That was a test file and actual file has 1200 rows and 69 columns. Is there any method/ formula/ function you used to made it into output sheet2?

Thanks,

There is a code attached with the book. While at excel do the following
Alt+F8 will take to the actual code. It is called Module that is where the code is stored.
Or click developer then visual basics is also the same.
While you are at module, you hit F5, or from the menu above click run, then press to run.
The code should cope with any amount of data. The only thing you need to watch is "Currentregion" You should not have a whole column, or row empty in the input data, but can easily change.
To run the code in your actual data:
If you sheet names are different to sheets1 and 2, change them. If you are not sure, I will do it for you.

Hello AB33,

Thank you for your help. However as I have not much knowledge on VBs etc my result is coming up different I think. I attached one of the actual files, could you perform convertion, please and what do I need to do if want to keep it as an template for future. Please advice.

Thank you

I have also added a button on sheet1 column AG, so when you want to re-run the code, just click the button

It works like a magic. Thank you.

Misheel,
You are welcome!
If you have any issue, PM me.
Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.