none
Excel VBA Macro to Transpose Vertical Data to Horizontal Data RRS feed

  • Question

  • Hi

    I am looking for someone who can help me with a little more complicated "transpose data" issue. I want to transpose two row different rows and copy one column into another column as a 'Paste Link'. Here are the rules:

    (1) Transpose date (contains 133 values) into column A.

    (2) Transpose Quantity (contains 133 values) into column B.

    (3) Copy-paste product into column C as a 'Link Paste' format and paste it down until it reach 133 values.

    This flow should be repeated until it finish the whole column (products) <- in that column are there more than 3000 values.

    rule nr. (1) should be fix and always refer to the date in row 1. But (2) and (3) should change row as the process continues.

    That means each time it has transposed the three above, it a new process should start right below.  

    Here is an example of the current data        

       Xdate Xdate Xdate Xdate Xdate Xdate Xdate
    212086 0 0 0 0 0 0 0 0
    211459 2 0 0 0 0 0           
    212429 0 3 0 0 0
    212419 0 0 0 0 0 0 0 0
    591066 0 0 0 0 0 0 0 0

    And I want it to show like this:

    Date   Quantity  Product name

    Xdate   0           2120860

    Xdate   0           2114592

    I hope somone kind friend will help me with this task. Would be greatly appreciated! :)


    Sunday, June 18, 2017 2:53 PM

All replies

  • Hi Sivansen,

    Thanks for visiting our forum.

    Then here we mainly discuss questions about Excel User Interface. As your query is about VBA macro, we'll move your question to the MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, June 19, 2017 2:13 AM
  • Hi Sivansen,

    What do you mean "Link Paste" format? Maybe you could try this code.

    Sub DD()
    
    Dim ws1 As Worksheet
    
    Set ws1 = Sheets("Sheet1")
    
    Dim ws2 As Worksheet
    
    Set ws2 = Sheets("Sheet2")
    
    Dim count As Integer
    
    Dim rng As Range
    
    Set rng = ws1.UsedRange
    
    ws2.Cells(1, 1) = "Date"
    
    ws2.Cells(1, 2) = "Quantity"
    
    ws2.Cells(1, 3) = "Product name"
    
    For i = 1 To rng.Columns.count
    
    For j = 2 To rng.Rows.count
    
    count = ws2.Range("A" & ws2.Rows.count).End(xlUp).Row
    
    ws2.Cells(count + 1, 1) = rng.Cells(1, i)
    
    ws2.Cells(count + 1, 2) = rng.Cells(j, i)
    
    ws2.Cells(count + 1, 3) = rng.Cells(j, 1)
    
    Next j
    
    Next i
    
    End Sub

    It will transpose data like this

    Before


    After


    Best Regards,

    Terry

    Tuesday, June 20, 2017 6:09 AM