Transpose Horizontal to vertical

[jeyachandran]

Hi sir,
I need macro or formula for transpose horizontal data to vertical data. sample file attached for reference.

Note:
* Sheet1 example data to be transposed in sheet2
* Data to be transpose from 2 to Last row and 2 to last column
* Result should be in sheet2 in same format.

 

[Marc L]

Hi,​

​

according to your attachment a VBA demonstration as a beginner starter​

to paste to the Sheet2 worksheet module :​

Sub Demo1()
        Me.UsedRange.Offset(1).Clear
        Application.ScreenUpdating = False
        L& = 2
        R& = 2
    With Sheet1
        While .Cells(R, 2).Text > ""
            With .Cells(R, 1)
                C% = .End(xlToRight).Column - 1
                .Copy Cells(L, 1).Resize(C \ 2)
            End With
            For C = 2 To C Step 2
                .Cells(R, C).Resize(, 2).Copy Cells(L, 2)
                L = L + 1
            Next
                R = R + 1
        Wend
    End With
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[AlanSidman]

With Power Query, an alternative solution

l

 

[jeyachandran]

Hi sir,

Macro Code works fine.. but it takes too time for large number of data (ie more than 100 rows), So Could u resolve it?

 

[Marc L]

As I wrote : « according to your attachment » and important information must be in the initial post …​

​

Try the Power Query / Get And Transform way like in post #3.​

​

 

[jeyachandran]

hi sir Thanks for your reply, am using excel 2007 version, so i couldn't use power query, so could you please suggest any formula that...

 

[jeyachandran]

Hi sir,
I got piece of code from MR.excel website, the code works fine,the result to be populated in same sheet but when run the code for large no of data..runtime error 9 subscript out of range will be displayed.So any modifications for this code?

>>> use code - tags <<<

Sub test()
    Dim a, b(), i As Long, ii As Long, n As Long
    With Range("a1").CurrentRegion
        a = .Value
        ReDim b(1 To Application.CountA(.Cells) / 3, 1 To 3)
        For i = 2 To UBound(a, 1)
            For ii = 2 To UBound(a, 2) Step 2
                If a(i, ii) <> "" Then
                    n = n + 1
                    b(n, 1) = a(i, 1)
                    b(n, 2) = a(i, ii)
                    b(n, 3) = a(i, ii + 1)
                End If
            Next
        Next
        With .Offset(, .Columns.Count + 1)
            .Resize(1, 3).Value = Range("a1:c1").Value
            .Offset(1).Resize(n, 3).Value = b
        End With
    End With
End Sub

 

[Marc L]

No any modification here (bad dim logic, losing original format, this kind of code just needs 15 codelines)​

'cause you have committed some wild cross posting ‼ So just ask on the original site.​

​

You must respect forum rules, use the code tags here via the 3 dots icon …​

Cross posting is only tolerated if you post on every forum links to others where you have opened the same question.​

​

And the better initial post - complete crystal clear explanation & attachment well reflecting the reality - the better targeted solution …​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[jeyachandran]

Thanks for your advise..these problem will never happens again.

 

[pike]

consider an array

Option Explicit
Sub TransposePaste_Index_dbArray()
 Dim dbArray
 With Sheets("Data Sheet Copy")'Rename
 dbArray = .[A1].CurrentRegion
 End With
 With Sheets("NewSheet")'Rename destinaltion
 .[A1].Resize(UBound(dbArray, 2), UBound(dbArray)) = Application.Transpose(Application.Index(dbArray, Evaluate("Row(1:" & UBound(dbArray) + 1 & ")"), Evaluate("Column(1:" & UBound(dbArray, 2) + 1 & ")")))
 End With
End Sub

 

[Marc L]

these problem will never happens again.

So do the necessary as expected in post #8 like in forum rules if you want further help here for your transpose copy need​

if the code hint given within post #8 -at beginner level - was not seen for any good enough reader …​

​