hi frends!!

[antonpogi]

hi im just new here.. i want to transpose the values in a column to a row and when i update the values in the column it should be transposed to the next blank row.. pls help.. thanks

RAW DATA
<TABLE style="WIDTH: 56pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=74 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=74 height=18 x:str="apple ">apple </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>banana</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>mango</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>grapes</TD></TR></TBODY></TABLE>
OUTPUT
<TABLE style="WIDTH: 284pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=377 border=0 x:str><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 70pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=93 height=18 x:str="apple ">apple </TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 104pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=138>banana</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 45pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=60>mango</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=86>grapes</TD></TR></TBODY></TABLE>



UPDATED DATA
<TABLE style="WIDTH: 56pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=74 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=74 height=18>banana</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>apple</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>grapes</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>mango</TD></TR></TBODY></TABLE>OUTPUT
<TABLE style="WIDTH: 284pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=377 border=0 x:str><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 70pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=93 height=18 x:str="apple ">apple </TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 104pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=138>banana</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 45pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=60>mango</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=86>grapes</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 284pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=377 border=0 x:str><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 70pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=93 height=18>banana</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 104pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=138>apple</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 45pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=60>grapes</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=86>mango</TD></TR></TBODY></TABLE>

 

[Evagrius Ponticus]

Will the updated data always be in the same location? In other words, do you overwrite the raw data with updated data each time?

 

[Mike Blackman]

Hi Anton and welcome to the board,

To transpose data you can use Copy - Paste Special and there is an option for Transposing Data. Is the output in another sheet or area from the Raw Data and is it just the one column of data that needs transposing?

 

[#BoB#]

Hi,

You can transpose the data using "Paste Special" feature of Excel. However, there is also an Array function to do this "=Transpose()"

Check the help section in excel for details. Just to give a brief here's an example for you:

Raw Data:

ABC DEF GHI
123 456 789

Here, we have 2 rows and 3 columns. However, after transposing we will have 3 rows and 2 columns.

So, first you select the range where you want the final output (in this case, select 3 rows and 2 columns).

With the ranges still selected, type the formula "=Transpose()".

Within the bracket select the Raw Data (2 Rows and 3 Columns).

Now press Ctrl+Shift+Enter. (Pressing simply Enter won't give you the result.)

The final output will be:

ABC 123
DEF 456
GHI 789

Hope this helps.


~ChillaX!!!

 

[antonpogi]

yes, updated data always be in the same location and i want macro to transpose data to the next blank row automatically.. thanks..

 

[Peter_SSs]

antonpogi

Welcome to the MrExcel board!

I suspect we don't know enough about how your sheet is being populated and used, but see if this is headed in the right direction.

1. Right click the sheet name tab and choose "View Code".
2. Paste the code below into the main right hand pane that opoens at step 1.
3. Close the VB window.
4. Update your data.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Column <> 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Range("A2", Range("A" & Rows.Count).End(xlUp))<br>    myRange.Copy<br>    Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=<SPAN style="color:#00007F">True</SPAN><br>    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> myRange = <SPAN style="color:#00007F">Nothing</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>