Transform several rows into columns

K

krisde

New Member
Joined
Sep 18, 2014
Messages
3
Hi,
I have to transform a table with multiple repeatable rows into columns.
Tried do use the Offset function but did not work.
I am using Excel 2010.

My raw data looks like this

[TABLE="width: 639"]
<TBODY>[TR]
[TD]Application</SPAN>[/TD]
[TD]Attribute 1</SPAN>[/TD]
[TD]Value 1</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 2</SPAN>[/TD]
[TD]Value 2</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 3</SPAN>[/TD]
[TD]Value 3</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 4</SPAN>[/TD]
[TD]Value 4</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 5</SPAN>[/TD]
[TD]Value 5</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 6</SPAN>[/TD]
[TD]Value 6</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 7</SPAN>[/TD]
[TD]Value 7</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

And the result needs to look like this:

[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]Attribute 1[/TD]
[TD]Attribute 2[/TD]
[TD]Attribute 3[/TD]
[TD]Attribute 4[/TD]
[TD]Attribute 5[/TD]
[TD]Attribute 6[/TD]
[TD]Attribute 7[/TD]
[/TR]
[TR]
[TD]Application</SPAN>[/TD]
[TD]Value 1</SPAN>[/TD]
[TD]Value 2</SPAN>[/TD]
[TD]Value 3</SPAN>[/TD]
[TD]Value 4</SPAN>[/TD]
[TD]Value 5</SPAN>[/TD]
[TD]Value 6</SPAN>[/TD]
[TD]Value 7</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


As I get the raw data on regular basis and need to transform them every time I get them, I planned to hold them in one sheet and the transformed version of it in another sheet.

Can anyone help?

Thanks
 
Sort by date Sort by votes
Transposing your columns will do so. Easiest way to do this is to just copy your data, then paste special and ensure Transpose is checked. In Excel 2010, the options are also in the right-click context menu after you've copied your data.

i24taa.png

1zl3a0x.png

2q9hn3k.png


Edit: Oops, missed bswdchwartz's reply.
 
Last edited:
Upvote 0
easy enough with index()
Excel Workbook
ABCDEFGHIJKLM
1Attribute 1Value 1Attribute 1Attribute 2Attribute 3Attribute 4Attribute 5Attribute 6Attribute 700
2Attribute 2Value 2Value 1Value 2Value 3Value 4Value 5Value 6Value 700
3Attribute 3Value 3
4Attribute 4Value 4
5Attribute 5Value 5
6Attribute 6Value 6
7Attribute 7Value 7
Sheet1
 
Upvote 0
OK, forgot to mention that I have about 1700 lines in the table, so after these 7 attributes the next application with 7 attributes follows and so on for over 250 applications.

For those 7 entries it would be easy, agree.
Thoiught to use a transpose function with offset in a loop till end of the table, but failed to get it running.

Thanks
DK
 
Upvote 0
like this then ?
Excel Workbook
ABCDEFGHIJKL
1Attribute 1Value 1Attribute 1Attribute 2Attribute 3Attribute 4Attribute 5Attribute 6Attribute 7
2Attribute 2Value 2Value 1Value 2Value 3Value 4Value 5Value 6Value 7
3Attribute 3Value 3attribute x 1attribute x 2attribute x 3attribute x 4attribute x 5attribute x 6attribute x 7
4Attribute 4Value 4value x1value x2value x3value x4value x5value x6value x7
5Attribute 5Value 5attribute Z 1attribute Z 2attribute Z 3attribute Z 4attribute Z 5attribute Z 6attribute Z 7
6Attribute 6Value 6value Z 1value Z 2value Z 3value Z 4value Z 5value Z 6value Z 7
7Attribute 7Value 70000000
8attribute x 1value x10000000
9attribute x 2value x2
10attribute x 3value x3
11attribute x 4value x4
12attribute x 5value x5
13attribute x 6value x6
14attribute x 7value x7
15attribute Z 1value Z 1
16attribute Z 2value Z 2
17attribute Z 3value Z 3
18attribute Z 4value Z 4
19attribute Z 5value Z 5
20attribute Z 6value Z 6
21attribute Z 7value Z 7
Sheet1
 
Upvote 0
[TABLE="width: 639"]
<TBODY>[TR]
[TD]No, the source looks like this:
Application 1</SPAN>[/TD]
[TD]
Attribute 1</SPAN>[/TD]
[TD]
Value 1</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 2</SPAN>[/TD]
[TD]Value 2</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 3</SPAN>[/TD]
[TD]Value 3</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 4</SPAN>[/TD]
[TD]Value 4</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 5</SPAN>[/TD]
[TD]Value 5</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 6</SPAN>[/TD]
[TD]Value 6</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 7</SPAN>[/TD]
[TD]Value 7</SPAN>[/TD]
[/TR]
[TR]
[TD]Application 2</SPAN>[/TD]
[TD]Attribute 1</SPAN>[/TD]
[TD]Value 11</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 2</SPAN>[/TD]
[TD]Value 12</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 3</SPAN>[/TD]
[TD]Value 13</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 4</SPAN>[/TD]
[TD]Value 14</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 5</SPAN>[/TD]
[TD]Value 15</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 6</SPAN>[/TD]
[TD]Value 16</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 7</SPAN>[/TD]
[TD]Value 17</SPAN>[/TD]
[/TR]
[TR]
[TD]Application 3</SPAN>[/TD]
[TD]Attribute 1</SPAN>[/TD]
[TD]Value 21</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 2</SPAN>[/TD]
[TD]Value 22</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 3</SPAN>[/TD]
[TD]Value 23</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 4</SPAN>[/TD]
[TD]Value 24</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 5</SPAN>[/TD]
[TD]Value 25</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 6</SPAN>[/TD]
[TD]Value 26</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 7</SPAN>[/TD]
[TD]Value 27</SPAN>[/TD]
[/TR]
[TR]
[TD]Application 4</SPAN>[/TD]
[TD]Attribute 1</SPAN>[/TD]
[TD]Value 31</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 2</SPAN>[/TD]
[TD]Value 32</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 3</SPAN>[/TD]
[TD]Value 33</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 4</SPAN>[/TD]
[TD]Value 34</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 5</SPAN>[/TD]
[TD]Value 35</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 6</SPAN>[/TD]
[TD]Value 36</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Attribute 7</SPAN>[/TD]
[TD]Value 37</SPAN>[/TD]
[/TR]
[TR]
[TD]And so on ....
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
what's it supposed to end up like then?
this maybe?
Excel Workbook
ABCDEFGHIJKL
1app 1Attribute 1Value 1Attribute 1Attribute 2Attribute 3Attribute 4Attribute 5Attribute 6Attribute 7
2Attribute 2Value 2app 1Value 1Value 2Value 3Value 4Value 5Value 6Value 7
3Attribute 3Value 3app 2value x1value x2value x3value x4value x5value x6value x7
4Attribute 4Value 4app3value Z 1value Z 2value Z 3value Z 4value Z 5value Z 6value Z 7
5Attribute 5Value 5
6Attribute 6Value 6
7Attribute 7Value 7
8app 2Attribute 1value x1
9Attribute 2value x2
10Attribute 3value x3
11Attribute 4value x4
12Attribute 5value x5
13Attribute 6value x6
14Attribute 7value x7
15app3Attribute 1value Z 1
16Attribute 2value Z 2
17Attribute 3value Z 3
18Attribute 4value Z 4
19Attribute 5value Z 5
20Attribute 6value Z 6
21Attribute 7value Z 7
Sheet1 (2)
Excel Workbook
E
2app 1
Sheet1 (2)
Excel Workbook
F
2Value 1
Sheet1 (2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA code HTML text transformation
Replies
4
Views
248
Sia
S
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
191
montecarlo2012
montecarlo2012
E
  • Question Question
Adding a custom message.
Replies
1
Views
709
Micron
M
T
  • Solved
Extracting and Formatting Numbers from Data Rows
2
Replies
12
Views
451
TheWes
T
S
  • Solved
Five Values that Equal a Sum Value
2
Replies
12
Views
226
ststern45
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top