Transpose from Rows to Columns

C

chiswickbridge

Board Regular
Joined
Feb 2, 2013
Messages
130
I have a Database of Mutual Fund Navs...
It has 4 columns


Scheme Code : It is a 6 digit string
Scheme Name : String
Net Asset : Numeral with 2 decimal place
Value Date : Date


I import the data from a Webpage to Excel and Formatted as above.


The size of the data will increase depending on Scheme Codes and the Range dates


Need a VBA to Transpose the data.


The VBA must create a new Worksheet, with Scheme Code as the Worksheet name, cell A1 will have the Scheme name.


Row 3 will the Value date in ascending order ie from 01Jan to 30Jan
Row 4 will have the corresponding Net Asset.


With the above transposing, I can create a Graph and evaluate the trend....

Thanks Guys.....


Scheme Code Scheme Name Net Asset Value Date


118826 / Mirae Asset India Equity Fund - Direct Plan - Dividend / 37.345 / 01-Jan-18
118826 / Mirae Asset India Equity Fund - Direct Plan - Dividend / 37.276 / 02-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 37.336 03-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 37.633 04-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 37.932 05-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.075 08-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.072 09-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 37.995 10-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.048 11-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.161 12-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.374 15-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.084 16-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.412 17-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.298 18-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.577 19-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.706 22-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 39.127 23-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 39.079 24-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.962 25-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 39.047 29-Jan-18
118826 Mirae Asset India Equity Fund - Direct Plan - Dividend 38.778 30-Jan-18


118825 Mirae Asset India Equity Fund - Direct Plan - Growth 50.148 01-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 50.055 02-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 50.136 03-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 50.535 04-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 50.937 05-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.129 08-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.125 09-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.021 10-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.092 11-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.244 12-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.53 15-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.14 16-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.581 17-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.428 18-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.803 19-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.975 22-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 52.542 23-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 52.476 24-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 52.32 25-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 52.433 29-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 52.072 30-Jan-18
118825 Mirae Asset India Equity Fund - Direct Plan - Growth 51.799 31-Jan-18


107579 Mirae Asset India Equity Fund - Dividend Plan 20.763 01-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 20.723 02-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 20.756 03-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 20.921 04-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.087 05-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.165 08-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.163 09-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.12 10-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.148 11-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.211 12-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.328 15-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.166 16-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.348 17-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.284 18-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.439 19-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.509 22-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.743 23-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.715 24-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.65 25-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.695 29-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.546 30-Jan-18
107579 Mirae Asset India Equity Fund - Dividend Plan 21.432 31-Jan-18


107578 Mirae Asset India Equity Fund - Growth Plan 48.039 01-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 47.949 02-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 48.025 03-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 48.406 04-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 48.79 05-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 48.972 08-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 48.967 09-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 48.866 10-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 48.933 11-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 49.078 12-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 49.348 15-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 48.974 16-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 49.395 17-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 49.248 18-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 49.605 19-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 49.767 22-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 50.308 23-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 50.245 24-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 50.094 25-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 50.199 29-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 49.853 30-Jan-18
107578 Mirae Asset India Equity Fund - Growth Plan 49.59 31-Jan-18


118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 50.482 01-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 50.214 02-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 50.406 03-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 50.844 04-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.511 05-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.696 08-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.836 09-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.638 10-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.628 11-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.813 12-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.911 15-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.083 16-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.279 17-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 50.616 18-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.083 19-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.178 22-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.561 23-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 51.331 24-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 50.96 25-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 50.744 29-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 50.266 30-Jan-18
118835 Mirae Asset Emerging Bluechip Fund - Direct Plan - Dividend 49.862 31-Jan-18


118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 55.367 01-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 55.073 02-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 55.284 03-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 55.765 04-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.496 05-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.699 08-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.852 09-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.636 10-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.624 11-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.827 12-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.935 15-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.027 16-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.241 17-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 55.514 18-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.027 19-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.131 22-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.552 23-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 56.299 24-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 55.893 25-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 55.655 29-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 55.131 30-Jan-18
118834 Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth 54.687 31-Jan-18
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Try this macro. It assumes that there are no blank rows in your data. It also assumes your data sheet is named "Sheet1". Change the name in the code to suit your needs.
Code: 
Sub TransposeData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim rngUniques As Range, SC As Range, srcWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    srcWS.Range("A1:A" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:A" & LastRow), Unique:=True
    Set rngUniques = srcWS.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
    srcWS.Range("A1").AutoFilter
    For Each SC In rngUniques
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = CStr(SC)
        Range("A1") = CStr(SC)
        srcWS.Range("A1:D" & LastRow).AutoFilter Field:=1, Criteria1:=SC
        srcWS.Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        Cells(3, 1).PasteSpecial Transpose:=True
        srcWS.Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        Cells(4, 1).PasteSpecial Transpose:=True
    Next SC
    srcWS.Range("A1").AutoFilter
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
The ADO Recordset.GetRows method is kind of like a transposed Range.CopyFromRecordSet method.
Code: 
Sub TransposeTable()
Dim aCn As Object, aRs1 As Object, aRs2 As Object, sql As String
Const DATASHEET As String = "Data" ' The name of the worksheet with the imported data
  Set aCn = CreateObject("ADODB.Connection")
  Set aRs1 = CreateObject("ADODB.Recordset")
  Set aRs2 = CreateObject("ADODB.Recordset")
  With aCn
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & ThisWorkbook.Name & "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
    .Open ' if this line errors, install the ACE driver from https://www.microsoft.com/en-us/download/details.aspx?id=23734
  End With
  sql = "SELECT DISTINCT [Scheme Code], [Scheme Name] FROM [" & DATASHEET & "$] WHERE [Scheme Code] IS NOT NULL ORDER BY [Scheme Code] DESC"
  aRs1.Open sql, aCn, 3, 1, 1
  sql = "SELECT [Scheme Code], [Value Date], [Net Asset] FROM [" & DATASHEET & "$] ORDER BY [Value Date]"
  aRs2.Open sql, aCn, 3, 1, 1
  While Not aRs1.EOF
    With Worksheets.Add(After:=Worksheets(DATASHEET))
      .Name = aRs1(0)
      .Cells(1) = aRs1(1)
      aRs2.Filter = "[Scheme Code] = " & aRs1(0)
      .Cells(2, 1).Resize(2, aRs2.RecordCount) = aRs2.GetRows(, , Array(1, 2))
    End With
    aRs1.MoveNext
  Wend
  aCn.Close
  Set aCn = Nothing
  Set aRs1 = Nothing
  Set aRs2 = Nothing
End Sub
 
Last edited:
Upvote 0
WOW...Thanks MUMPS ( You helped me earlier as well and JPVH8 ( Fantastic for a new joiner of 2 days old ) ....Works excellent.....Both are just what I needed....However, I missed mentioning.......I have select scheme codes, saved in a worksheet called "Record" for A1 to A100..... can the VBA select only these scheme codes and Transpose them.....Sorry for the additional work.... Thanks Guys !!!
 
Upvote 0
Little confused... in what I want....


However, I missed mentioning.......I have select scheme codes, saved in a worksheet called "Record" for A1 to A100..... can the VBA select only these scheme codes.....Sorry for the additional work.... Thanks Guys !!!


Is it possible of fill 2 columns instead of 2 rows ?


The VBA must create a new Worksheet, with Scheme Code as the Worksheet name, cell A1 will have the Scheme name.


Cell(2, 1) = "Value Date"
Cell(2, 2) = "Net Asset"
Cell(3, 1) will the Value date in ascending order ie from 01Jan to 30Jan....Column 1
Cell(3, 2) will have the corresponding Net Asset....in Column 2.




With the above, I can create a Dynamic Graph and evaluate the trend for any range of dates....
 
Upvote 0
For consistency, add a Header value of "Scheme Code" to the column on your Record worksheet.
Code: 
Sub TransposeTable()
Dim aCn As Object, aRs1 As Object, aRs2 As Object, sql As String
Const DATASHEET As String = "Data", RECORDSHEET As String = "Record"
  Set aCn = CreateObject("ADODB.Connection")
  Set aRs1 = CreateObject("ADODB.Recordset")
  Set aRs2 = CreateObject("ADODB.Recordset")
  With aCn
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & ThisWorkbook.Name & "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
    .Open ' if this line errors, install the ACE driver from https://www.microsoft.com/en-us/download/details.aspx?id=23734
  End With
  sql = "SELECT DISTINCT d.[Scheme Code], d.[Scheme Name] " & _
        "FROM [" & DATASHEET & "$] AS d INNER JOIN [" & RECORDSHEET & "$] AS r ON d.[Scheme Code] = r.[Scheme Code] " & _
        "WHERE d.[Scheme Code] IS NOT NULL ORDER BY d.[Scheme Code] DESC"
  aRs1.Open sql, aCn, 3, 1, 1
  sql = "SELECT [Value Date], [Net Asset], [Scheme Code] FROM [" & DATASHEET & "$] WHERE [Value Date] IS NOT NULL ORDER BY [Value Date]"
  aRs2.Open sql, aCn, 3, 1, 1
  While Not aRs1.EOF
    With Worksheets.Add(After:=Worksheets(DATASHEET))
      .Name = aRs1(0)
      .Cells(1) = aRs1(1)
      .Cells(2, 1) = aRs2.Fields(0).Name: .Cells(2, 2) = aRs2.Fields(1).Name
      aRs2.Filter = "[Scheme Code] = " & aRs1(0)
      .Activate ' workaround for an Excel CopyFromRecordset bug that formats cells on Data worksheet
      .Cells(3, 1).CopyFromRecordset aRs2, , 2
    End With
    aRs1.MoveNext
  Wend
  aCn.Close
  Set aCn = Nothing
  Set aRs1 = Nothing
  Set aRs2 = Nothing
End Sub
 
Upvote 0
Excellent !!!.... Thank you JVPH8.... Your programming style is complex and crisp.....Job done in an efficient way...Thanks
 
Upvote 0
Hi Guys.... I hope I am not extending my luck too far.....


My issues are well resolved by the above fantastic VBA solution....But I need to resolve so other issues....


To generate my Excel database, I need to import data from a Text file, whose size reaches upto 20MB


The Text File has 4 data columns with a semi-colon ( ; ) as a delimiter :


Scheme Code : It is a 6 digit string
Scheme Name : String
Net Asset : Numeral with 2 decimal place
Value Date : Date


I can easily Import using the data Importer from Text Wizard, but the Full data is imported.


What I want is... to Import Select Scheme Code, but within 2 specific dates... This will reduce the import time and give me a crispier Dynamic Graph of a certain Time Frame.


I have a Header value of "Scheme Code" on my Record worksheet in Column 1. And I can add in Column 3...the 2 cells for the date range...


This way when I import, the VBA is select only those Scheme Code listed and within the time Frame...


Hope above is clear....and a Huge Thanks in advance....
 
Upvote 0
Small addition.....The text file will be in any Folder an on any drive.....So the VBA should open Explorer to select the correct file...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
How to count certain month from a column
Replies
3
Views
383
Joe4
Joe4
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
852
paulkc
P
V
  • Solved
sum table based on rows and multiple years columns
Replies
2
Views
509
vampsthevampyre
V
J
  • Question Question
Need assistance with auto disaggregation of data from monthly to weekly
Replies
1
Views
446
jajatidev
J
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
513
Eric W
Eric W

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top