tranpose range of column data into row

[seowfung75]

I have a raw data set that looks like following

ID DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 DATA7 DATA8
a 15 3 4 1 13 5 5 6
b 12 15 8 15 10 13 8 4
c 3 1 1 13 9 13 15 13
d 13 3 2 4 3 12 14 8
e 10 12 7 8 1 2 9 10

I want to transpose the data including the header to something like this

ID TYPE DATA
a DATA1 15
a DATA2 3
a DATA3 4
a DATA4 1
a DATA5 13
a DATA6 5
a DATA7 5
a DATA8 6
b DATA1 12
b DATA2 15
b DATA3 8
b DATA4 15
b DATA5 10
b DATA6 13
b DATA7 8
b DATA8 4
c DATA1 3
c DATA2 1
c DATA3 1
c DATA4 13
c DATA5 9
c DATA6 13
c DATA7 15
c DATA8 13
d…
d…
d…
..
…
…
e…
e…
…

[AndyLitch]

1. Select your data
2. Select copy
3. Select the top left cell location you want the transposed data to go
4. Select paste special
5. At the bottom of the paste special popup - select the transpose checkbox.

[AB33]

Please attach a sample book, one sheet should show your raw data and another sheet your desired result(Output). To attach a sample, go to advance then attachment

[AB33]

I have created a sheet based on the picture you have attached. If your sheet name is not sheet1, you need to change it to your actual sheet name. There is a button on sheet1

[Marcol]

This workbook gives some formula examples you might be able to work with.
This is for 2007 and above.

Put your data to transpose in an Excel Table.

[EDIT]
I'll add the file when the Tech Team fixes the "Manage Attachments" button in "Go Advanced"

[seowfung75]

attached is the sample workbook.
The sample rawdata only contains data upto column I. TThe actual dataset is relatively large, the data is filled till column RS, with 11000 thousand rows.

i would like to have the converted data to be dumbed to a new tab, is that possible?

[seowfung75]

sample data attached

[AB33]

Seow,
My above code does exactly. You need to change the name of sheet1 in to raw data

[Marcol]

This is the attachment I couldn't upload earlier in Post#5

Make your data an Excel Table for this to work.

[seowfung75]

Any idea what does this line do?

.Range("A2").Resize(k, 3).Value = y()

I am getting error message

[AB33]

I am lost!
I have attached your sample with the result. The code creates new sheet. Have you run the code? Is your data output in 3 columns?

[Marcol]

Here's your sample transposed using formulae for 2003.

[seowfung75]

I believe it works now, it must have been some flaws in my 11000 row dataset.
Thanks very much AB33.

[AB33]

Seow,
The code could handle up to excel limit of 1m something as long as you do not have entire row or column empty in your raw data. You do not have to worry about output, the code does create a new sheet.

Marcol has also provided you a formula solution with two different version of excels.

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.