Excel 2007 : Converting horizontal data to vertical data (more than just transposing)

Hi,

I have data in a horizontal format that I would like to convert into a vertical format.

The following is an extract of the data -:

Postcode Trade 01-Apr 02-Apr 03-Apr 04-Apr 05-Apr
AB A 1 1 1 1 2
AB B 1 1 1 0 1
AB C 1 1 1 1 2
AB D 3 3 2 2 4
AB E 0 0 0 0 1
AB F 1 1 0 0 1
AB G 0 0 0 0
AL A 3 3 2 2 4
AL B 1 1 1 1 2
AL C 1 1 1 1 2
AL D 3 3 2 2 4
AL E 6 6 5 4 8
AL F 1 1 1 0 1
AL G 1 1 1 1 2
AL H 1 1 0 0 1

The columns extend right out to 31-Mar i.e one for each day of the year.
----------------------------------------------------------------------------------------------------------------------------------

I have been asked to present the data, vertically, as follows -:

Postcode Date Trade Amount
AB 01-Apr-10 A 1
AB 02-Apr-10 B 1
AB 03-Apr-10 C 1
AB 04-Apr-10 D 1
AB 05-Apr-10 E 2

i.e. each day of the year has to be listed against each category in the Trade column. This makes a total of over 300k rows. I have went through the painful process of copying and pasting (transposing) and I have done all the postcodes and dates.

Would anybody know any way of formulating the amount across from horizontal to vertical? I have looked into a few different formulas to no avail.

Thanks

This will do it, but it will take a while on that large a data set. Run this on a copy of your sheet.

Please Login or Register  to view this content.

=============
How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

I wrote it for you based on your sample data.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.