Transform several rows into columns

[krisde]

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

Application</SPAN>	Attribute 1</SPAN>	Value 1</SPAN>
	Attribute 2</SPAN>	Value 2</SPAN>
	Attribute 3</SPAN>	Value 3</SPAN>
	Attribute 4</SPAN>	Value 4</SPAN>
	Attribute 5</SPAN>	Value 5</SPAN>
	Attribute 6</SPAN>	Value 6</SPAN>
	Attribute 7</SPAN>	Value 7</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

And the result needs to look like this:

	Attribute 1	Attribute 2	Attribute 3	Attribute 4	Attribute 5	Attribute 6	Attribute 7
Application</SPAN>	Value 1</SPAN>	Value 2</SPAN>	Value 3</SPAN>	Value 4</SPAN>	Value 5</SPAN>	Value 6</SPAN>	Value 7</SPAN>

<TBODY>
</TBODY>

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

 

[bschwartz]

Hi Krisde,

Welcome to the forums!

Try using the TRANSPOSE function.

 

[Benvolio]

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.

Edit: Oops, missed bswdchwartz's reply.

 

[martindwilson]

easy enough with index()

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Attribute 1	Value 1			Attribute 1	Attribute 2	Attribute 3	Attribute 4	Attribute 5	Attribute 6	Attribute 7	0	0
2	Attribute 2	Value 2			Value 1	Value 2	Value 3	Value 4	Value 5	Value 6	Value 7	0	0
3	Attribute 3	Value 3
4	Attribute 4	Value 4
5	Attribute 5	Value 5
6	Attribute 6	Value 6
7	Attribute 7	Value 7
Sheet1

 

[krisde]

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

 

[martindwilson]

like this then ?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Attribute 1	Value 1			Attribute 1	Attribute 2	Attribute 3	Attribute 4	Attribute 5	Attribute 6	Attribute 7
2	Attribute 2	Value 2			Value 1	Value 2	Value 3	Value 4	Value 5	Value 6	Value 7
3	Attribute 3	Value 3			attribute x 1	attribute x 2	attribute x 3	attribute x 4	attribute x 5	attribute x 6	attribute x 7
4	Attribute 4	Value 4			value x1	value x2	value x3	value x4	value x5	value x6	value x7
5	Attribute 5	Value 5			attribute Z 1	attribute Z 2	attribute Z 3	attribute Z 4	attribute Z 5	attribute Z 6	attribute Z 7
6	Attribute 6	Value 6			value Z 1	value Z 2	value Z 3	value Z 4	value Z 5	value Z 6	value Z 7
7	Attribute 7	Value 7			0	0	0	0	0	0	0
8	attribute x 1	value x1			0	0	0	0	0	0	0
9	attribute x 2	value x2
10	attribute x 3	value x3
11	attribute x 4	value x4
12	attribute x 5	value x5
13	attribute x 6	value x6
14	attribute x 7	value x7
15	attribute Z 1	value Z 1
16	attribute Z 2	value Z 2
17	attribute Z 3	value Z 3
18	attribute Z 4	value Z 4
19	attribute Z 5	value Z 5
20	attribute Z 6	value Z 6
21	attribute Z 7	value Z 7
Sheet1

 

[krisde]

No, the source looks like this: Application 1</SPAN>	Attribute 1</SPAN>	Value 1</SPAN>
	Attribute 2</SPAN>	Value 2</SPAN>
	Attribute 3</SPAN>	Value 3</SPAN>
	Attribute 4</SPAN>	Value 4</SPAN>
	Attribute 5</SPAN>	Value 5</SPAN>
	Attribute 6</SPAN>	Value 6</SPAN>
	Attribute 7</SPAN>	Value 7</SPAN>
Application 2</SPAN>	Attribute 1</SPAN>	Value 11</SPAN>
	Attribute 2</SPAN>	Value 12</SPAN>
	Attribute 3</SPAN>	Value 13</SPAN>
	Attribute 4</SPAN>	Value 14</SPAN>
	Attribute 5</SPAN>	Value 15</SPAN>
	Attribute 6</SPAN>	Value 16</SPAN>
	Attribute 7</SPAN>	Value 17</SPAN>
Application 3</SPAN>	Attribute 1</SPAN>	Value 21</SPAN>
	Attribute 2</SPAN>	Value 22</SPAN>
	Attribute 3</SPAN>	Value 23</SPAN>
	Attribute 4</SPAN>	Value 24</SPAN>
	Attribute 5</SPAN>	Value 25</SPAN>
	Attribute 6</SPAN>	Value 26</SPAN>
	Attribute 7</SPAN>	Value 27</SPAN>
Application 4</SPAN>	Attribute 1</SPAN>	Value 31</SPAN>
	Attribute 2</SPAN>	Value 32</SPAN>
	Attribute 3</SPAN>	Value 33</SPAN>
	Attribute 4</SPAN>	Value 34</SPAN>
	Attribute 5</SPAN>	Value 35</SPAN>
	Attribute 6</SPAN>	Value 36</SPAN>
	Attribute 7</SPAN>	Value 37</SPAN>
And so on ....

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[martindwilson]

what's it supposed to end up like then?
this maybe?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	app 1	Attribute 1	Value 1			Attribute 1	Attribute 2	Attribute 3	Attribute 4	Attribute 5	Attribute 6	Attribute 7
2		Attribute 2	Value 2		app 1	Value 1	Value 2	Value 3	Value 4	Value 5	Value 6	Value 7
3		Attribute 3	Value 3		app 2	value x1	value x2	value x3	value x4	value x5	value x6	value x7
4		Attribute 4	Value 4		app3	value Z 1	value Z 2	value Z 3	value Z 4	value Z 5	value Z 6	value Z 7
5		Attribute 5	Value 5
6		Attribute 6	Value 6
7		Attribute 7	Value 7
8	app 2	Attribute 1	value x1
9		Attribute 2	value x2
10		Attribute 3	value x3
11		Attribute 4	value x4
12		Attribute 5	value x5
13		Attribute 6	value x6
14		Attribute 7	value x7
15	app3	Attribute 1	value Z 1
16		Attribute 2	value Z 2
17		Attribute 3	value Z 3
18		Attribute 4	value Z 4
19		Attribute 5	value Z 5
20		Attribute 6	value Z 6
21		Attribute 7	value Z 7
Sheet1 (2)

Excel Workbook
	E
2	app 1
Sheet1 (2)

Excel Workbook
	F
2	Value 1
Sheet1 (2)