Rows to Column on Same Sheet - Help

[jainson]

Hi,

I have a huge table which has 520 rows, 74 rows for each date. I want to make dates into column so that I have 74 rows and 7 columns in a new sheet.

I want the macro to help me do the same. Is it possible to do this?

Thanks!

 

[Trevor G]

Wont a Pivot Table give you this option. Can you show any sample data?

 

[Ruddles]

Is it possible? Yes, almost definitely - in fact I'd be stunned if a macro took more than 5-10 minutes to design and code from scratch.

But as Trevor says, seeing the data would be helpful. As the blocks of data in the table are quite large to display in a forum message, perhaps just a small sample will suffice, accompanied by an idea of what your desired output should look like?

(Some people prefer to see the entire table in order to work on it. File-sharing sites are blocked by my organisation's firewall but that's another possible method for making your data available to others.)

 

[jainson]

This is the sample of the data in the xls file I get as an output from the query in MS Access

<TABLE style="WIDTH: 848pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1126 border=0><COLGROUP><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6253" span=2 width=171><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" span=8 width=98><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 128pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=171 height=17>KART ROLLUP LEVEL 3</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 128pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=171>BAL_DESC</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/1/2011</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/4/2011</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/5/2011</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/6/2011</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/7/2011</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/8/2011</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/11/2011</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/12/2011</TD></TR><TR style="HEIGHT: 21.95pt; mso-height-source: userset" height=29><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 21.95pt; BACKGROUND-COLOR: transparent" height=29>BALSHEQY</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">ABCDE</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2500000</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2502536</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2505072</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2507608</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2510144</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2512680</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2515216</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2517752</TD></TR></TBODY></TABLE>

What I am required to do in a macro is:
1) export this data to a new xlsm file
2) delete first column
3) arrange the data in rows like the following:

BAL_DESC ABCDE
4/1/2011 2500000
4/4/2011 2502536
4/5/2011 2505072

and so on...

Hope that makes it a little more clear...

 

[Ruddles]

Before anyone starts writing code to do this, can I check something?

Do this manually in a copy of your workbook:-

• Delete column A of your worksheet
• Highlight the rest of the data on rows 1 & 2 as far as it extends to the right
• Right-click cell A3 and select Paste Special, then select Transpose and click OK
• Delete rows 1 & 2 entirely

Does that do what you want? If so, we can write a macro to replicate those steps fairly easily.

 

[jainson]

Yes that is cprrect and also the first feature I want is to export the data into a new xlsm file...

thank you for your help...

 

[Ruddles]

This macro will take your data as posted above and transpose it into a new workbook:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub TransposeData()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim ws As Worksheet
  Dim wkbk As Workbook
  Dim iLastCol As Integer
  
  Set ws = ThisWorkbook.Sheets(1)
  Set wkbk = Workbooks.Add
  
  iLastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
  
  ws.Range("B1").Resize(2, iLastCol - 1).Copy
  
  With wkbk.Sheets(1)
    .Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    .Columns("A:B").ColumnWidth = 100
    .Columns("A:B").EntireColumn.AutoFit
    .Rows("1:" & iLastCol).RowHeight = 30
    .Rows("1:" & iLastCol).EntireRow.AutoFit
  End With[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Sub[/FONT]

Start with your data in rows 1 & 2 of a worksheet and run the macro. It's up to you to save the new workbook as the code currently stands but it would be quite feasible to add a dialog box to allow you to navigate to a new filename.

Do you know how to run this code or do you need instructions?

 

[jainson]

Thanks a lot for the code. I do know how to run the same.

This code does serve the purpose. Just wanted to add a small feature, the actual data is mentioned in the .xls file named "Component_View_in_Excel" located at the location "K:\CLE03\Son" which would be open. I want to create a new .xlsm file named "Input_for_Cronos" at the same location and have this macro to run in that file.

How can I add this feature into the code?

Sorry if I am specifying this a little late...

Thanks again!!

 

[AHALEY]

Hi,

I have a huge table which has 520 rows, 74 rows for each date. I want to make dates into column so that I have 74 rows and 7 columns in a new sheet.

I want the macro to help me do the same. Is it possible to do this?

Thanks!

You can transpose your sheet by copying your table, then on a new sheet, right-click and "paste special". Then make sure that in the options, you have checked "transpose"

 

[Ruddles]

the actual data is mentioned in the .xls file named "Component_View_in_Excel" located at the location "K:\CLE03\Son" which would be open. I want to create a new .xlsm file named "Input_for_Cronos" at the same location and have this macro to run in that file.

That doesn't sound to tricky. The question is: where do you want this code to be located? Is it okay if it's in Component_View_in_Excel.xls?