Manipulating Data Entries with Header rows

[newyorkmetz12]

Hi i have list of all the past hurricanes and each obersvation lat and long the data is in the form



<TABLE style="WIDTH: 125pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=165 border=0 x:str><COLGROUP><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 44pt; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=58 height=17>MEDs</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 38pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=50>0umber</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 43pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=57>3 of t</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>********</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">********</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">*******</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Hour</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Lat.</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Lo0g.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12 UTC</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>18.3</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>60.7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>18 UTC</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>18.8</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>62.4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0 UTC</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>19.3</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>63.7</TD></TR></TBODY></TABLE>

This would be one entry then there are two blank lines followed by another entry, this repetes about 35,000 times

I need to take each entry and transpose them

e.g. for the above into


12UTC 18UTC 0 UTC
18.3 18.8 19.3
60.7 62.4 63.7

I dont want to copy each entery and then use past and select the options to transpose the data for each of the 35,000 entries, is there a script that can do this?

Any help is much appreciated.

 

[DonkeyOte]

copy A1:C3 go to say E1 and paste special - select values & check Transpose box

EDIT: It sounds as though you have too many rows of data to do this though as you have fewer columns available in XL than you do rows...

 

[newyorkmetz12]

But i have approximatly 30,000 differant storms each with a header file and two blank lines between them. This does not work?

I think you need a script but i don't know how to write one? Any Idea?

 

[newyorkmetz12]

what if i cut the sheet into 2. does anyone know a macro that can do this?

 

[Peter_SSs]

newyorkmetz12

Welcome to the MrExcel board!

Is this something like what you want? I suggest you try it on a smaller sample first.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TransposeHurricaneData()<br>    <SPAN style="color:#00007F">Dim</SPAN> AllAreas <SPAN style="color:#00007F">As</SPAN> Areas<br>    <SPAN style="color:#00007F">Dim</SPAN> SingleArea <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> AllAreas = Columns("A:C").SpecialCells(2).Areas<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> SingleArea <SPAN style="color:#00007F">In</SPAN> AllAreas<br>        <SPAN style="color:#00007F">With</SPAN> SingleArea<br>            .Offset(3).Resize(.Rows.Count - 3).Copy<br>            Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=<SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> SingleArea<br>    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    MsgBox "Transpose complete"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>