none
VB Script : Need assistance to transpose columns to rows RRS feed

  • Question

  • Hi Folks & Dave,

    I have a requirement to transpose a data export in column format, to rows.

    Here is my original data set :

    Cost Center,CONS Project,18-May,18-Jun,18-Jul,18-Aug
    US_4465,PFP-E0000001,1.5,1.5,2.3,2.5

    I need it to look like such:

    US_4465,PFP-E0000001,18-May,1.5
    US_4465,PFP-E0000001,18-Jun,1.5
    US_4465,PFP-E0000001,18-Jul,2.3
    US_4465,PFP-E0000001,18-Aug,2.5

    Could someone help me with the correct VB Script approach? I've tried countless example online with no luck.

    Thank you!

    Wednesday, October 4, 2017 6:47 PM

All replies

  • Not sure if you mean just VB or VBA because you have posted under a VBA forum so I have tested in VBA for Excel. Probably does not make any difference because the functionality will probably work wherever.

    I coded it somewhat generically so that it will not matter if you have additional dates in the first string with the additional values in the second string.

    Sub SplitStrings()
        Dim str1 As String
        Dim str2 As String
        Dim arrSplit1 As Variant
        Dim arrSplit2 As Variant
        Dim strResultInit As String
        Dim strResultExtended As String
        Dim i As Long
       
       
        str1 = "Cost Center,CONS Project,18-May,18-Jun,18-Jul,18-Aug"
        str2 = "US_4465 , PFP - E0000001, 1.5, 1.5, 2.3, 2.5"
       
        '***********************************************************************
        'Between the asterisk lines were additional tests with longer strings
        'str1 = "Cost Center,CONS Project,18-May,18-Jun,18-Jul,18-Aug,18-Sep"
        'str2 = "US_4465 , PFP - E0000001, 1.5, 1.5, 2.3, 2.5, 2.0"
       
        'str1 = "Cost Center,CONS Project,18-May,18-Jun,18-Jul,18-Aug,18-Sep,18-Oct"
        'str2 = "US_4465 , PFP - E0000001, 1.5, 1.5, 2.3, 2.5, 2.0, 2.1"
        '***************************************************************************
       
        arrSplit1 = Split(str1, ",")
        arrSplit2 = Split(str2, ",")
       
        'Split functionality always returns a one based array (even with Option Base 1 set)
        'OssieMac prefers to work with one based arrays
        ReDim Preserve arrSplit1(1 To UBound(arrSplit1) + 1)
        ReDim Preserve arrSplit2(1 To UBound(arrSplit2) + 1)

        strResultInit = arrSplit2(1) & "," & arrSplit2(2)   'Initial string in all results
       
        For i = 3 To UBound(arrSplit1)
            strResultExtended = strResultInit
            strResultExtended = strResultExtended & "," & arrSplit1(i) & "," & arrSplit2(i)
            Debug.Print strResultExtended
        Next i
    End Sub


    Regards, OssieMac

    Thursday, October 5, 2017 12:02 AM
  • Thank you, Ossie! I'm testing this out now and it doesn't seem to be firing...

    However, I wasn't clear earlier. My above data set from above is just an example. There could be up to 15k lines in excel that I need to transpose. Is there a way to make your code adapt to that and make it a bit more dynamic?

    I'd also prefer it it was in VB Script form (i'm sorry!) as I'll be using batch to kick these off.

    Thanks so much!!!



    • Edited by cdtakacs1 Thursday, October 5, 2017 7:38 AM
    Thursday, October 5, 2017 7:23 AM
  • Thank you, Ossie! I'm testing this out now and it doesn't seem to be firing...


    • Open a blank workbook and copy the code into the VBA editor
    • In the VBA editor use Ctrl and G to open the Immediate window.
    • Run the code (click anywhere in the code sub and press F5)
    • The Output will be in the Immediate window.

    I don't have VB script but I should not think that it will be much different from VBA and you should be able to convert it.

    Because you only provided example data which was sufficient for a single record, I assumed that when you knew how to break it up into the required components that you would be able to run a loop for multiple records. However, if you require more help to create the loop to run with multiple records then I will need a larger sample of data to test with so upload an example to OneDrive.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click in the field displaying the link and it should highlight and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)

    Regards, OssieMac

    Thursday, October 5, 2017 10:53 AM