Transposing Data in Excel

[SanketDash]

I need help in transposing data from rows to columns. Currently, my data is looking like this:[TABLE="width: 500"]
<tbody>[TR]
[TD]Y1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Y2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Y3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Y4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]

I want my data to look like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Y1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Y2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Y3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Y4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Y1[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Y2[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Y3[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Y4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Y1[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Y2[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Y3[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Y4[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]

I have mentioned the same numbers in each row for convenience purpose ony. In the actual data-set, the rows have different values.

 

[TheGlovner]

Your problem is a little confusing since the example doesn't support what you are actually asking for.

If the issue is that you truly want to "transpose" the data from rows to columns then there is a paste special option to do just that. Copy your data, then left click somewhere else (so as not to copy over any other data when your table is transposed), right click, paste special, transpose (Excel 2010, not sure if the path is the same on other versions, be surprised if it's vastly different though).

If that's not what your after you'll have to give a clearer explanation or a more accurate model. As the data truly transposed from rows to columns would look like:

Y1    Y2    Y3    Y4
1      1      1      1
2      2      2      2
3      3      3      3
etc...

 

[SanketDash]

"If that's not what your after you'll have to give a clearer explanation or a more accurate model. As the data truly transposed from rows to columns would look like:"

Thanks for the reply. I used the word transpose because I could not find a better word. Its more like arranging data in a specific format automatically.

The example mentions the required format.

 

[TheGlovner]

Okay, that changes things.

Probably a number of possible solutions but it will depend on any patterns in your data or required arrangement that we can construct rules around.

You could do it with a vlookup based on the Y1 - Y4, but the column which you wanted to return from the vlookup would change every four rows (as it switches back to Y1), so you'd need to add some way of the formula choosing the return column dynamically.

Other option would be some VBA, which will almost certainly take longer than a formula, and if it's not a repeatable process and this took longer to develop and test than just moving it manually (copy/paste) and using checksums to prove nothing has been lost/altered in the move, then it would be a false economy to spend the time developing a VBA solution.

 

[wynn89]

Hi,

are there many columns of data currently?

Is this going to be a routine process?

If not it might be easier to simply do a few copy and paste to reorder your data

Regards,
Wynn

 

[SanketDash]

@TheGlovener

VLOOKUP is a good option. It slipped my mind.

@Wynn
There are thousands of such blocks. Hence, I need an automatic way.

 

[TheGlovner]

As I say, it looks like you will need a way of calculating the return column from the VBA.

I would probably set up some adjacent column to hold the return colulmn as a hardcoded number for the first 4 rows.

So assuming we are only working with one column of the vertical data in your "transposed" table something like:




[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]C (shown as formula)
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Y1
[/TD]
[TD]=vlookup(A1, DataTable, C1, 0)
[/TD]
[TD]2
[/TD]
[TD]2 (first one hardcoded as column 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Y2
[/TD]
[TD]=vlookup(A2, DataTable, C2, 0
[/TD]
[TD]2
[/TD]
[TD]=if(A2="Y1", C1 + 1, C1)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Y3
[/TD]
[TD]=vlookup(A3, DataTable, C3, 0)
[/TD]
[TD]2
[/TD]
[TD]=if(A3="Y1", C2 + 1, C2)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Y4
[/TD]
[TD]=vlookup(A4, DataTable, C4, 0)
[/TD]
[TD]2
[/TD]
[TD]=if(A4="Y1", C3 + 1, C3)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Y1
[/TD]
[TD]=vlookup(A5, DataTable, C5, 0)
[/TD]
[TD]3
[/TD]
[TD]=if(A5="Y1", C4 + 1, C4)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Y2
[/TD]
[TD]=vlookup(A6, DataTable, C6, 0)
[/TD]
[TD]3
[/TD]
[TD]=if(A6="Y1", C5 + 1, C5)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Y3
[/TD]
[TD]=vlookup(A7, DataTable, C7, 0)
[/TD]
[TD]3
[/TD]
[TD]=if(A7="Y1", C6 + 1, C6)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Y4
[/TD]
[TD]=vlookup(A8, DataTable, C8, 0)
[/TD]
[TD]3
[/TD]
[TD]=if(A8="Y1", C7 + 1, C7)
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Obviously you'll need to expand that to have 4 formula and four column references but I think that should work.

So we use the occurrence of the string "Y1" in column A to trigger a change to the column number to be returned in the vlookup which we calculate independently in the adjacent column.

 

[SanketDash]

As I say, it looks like you will need a way of calculating the return column from the VBA.

I would probably set up some adjacent column to hold the return colulmn as a hardcoded number for the first 4 rows.

So assuming we are only working with one column of the vertical data in your "transposed" table something like:




[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]C (shown as formula)[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Y1[/TD]
[TD]=vlookup(A1, DataTable, C1, 0)[/TD]
[TD]2[/TD]
[TD]2 (first one hardcoded as column 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Y2[/TD]
[TD]=vlookup(A2, DataTable, C2, 0[/TD]
[TD]2[/TD]
[TD]=if(A2="Y1", C1 + 1, C1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Y3[/TD]
[TD]=vlookup(A3, DataTable, C3, 0)[/TD]
[TD]2[/TD]
[TD]=if(A3="Y1", C2 + 1, C2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Y4[/TD]
[TD]=vlookup(A4, DataTable, C4, 0)[/TD]
[TD]2[/TD]
[TD]=if(A4="Y1", C3 + 1, C3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Y1[/TD]
[TD]=vlookup(A5, DataTable, C5, 0)[/TD]
[TD]3[/TD]
[TD]=if(A5="Y1", C4 + 1, C4)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Y2[/TD]
[TD]=vlookup(A6, DataTable, C6, 0)[/TD]
[TD]3[/TD]
[TD]=if(A6="Y1", C5 + 1, C5)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Y3[/TD]
[TD]=vlookup(A7, DataTable, C7, 0)[/TD]
[TD]3[/TD]
[TD]=if(A7="Y1", C6 + 1, C6)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Y4[/TD]
[TD]=vlookup(A8, DataTable, C8, 0)[/TD]
[TD]3[/TD]
[TD]=if(A8="Y1", C7 + 1, C7)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Obviously you'll need to expand that to have 4 formula and four column references but I think that should work.

So we use the occurrence of the string "Y1" in column A to trigger a change to the column number to be returned in the vlookup which we calculate independently in the adjacent column.

Thanks a lot.