A simple transpose problem?

[frank933]

Guys I have a simple transpose problem to resolve. I hope I can find an answer here.

I have dataset in a bunch of rows but only 2 columns like so:

I would like to transpose? it and arrange it like so:

Is there a formula I can use on multiple rows to arrange the data in the above format?

Thank you all

 

[LegendaryAxe]

If you have a version of Excel that supports the TOROW function:

Book2
	A	B	C	D	E	F	G	H
1	A1	B1
2	A2	B2
3	A3	B3
4	A4	B4
5
6	A1	B1	A2	B2	A3	B3	A4	B4
Sheet1

Cell Formulas
Range	Formula
A6:H6	A6	=TOROW(A1:B4)
Dynamic array formulas.

 

[Logit]

VBA solution :

Option Explicit

Sub TransposeColumnsAB()
    Dim i As Long
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To lastRow
        Cells(1, (i - 1) * 2 + 1).Value = Cells(i, 1).Value
        Cells(1, (i - 1) * 2 + 2).Value = Cells(i, 2).Value
    Next i
End Sub

 

[frank933]

Thank you guys. I will try both solutions and report back.

 

[frank933]

If you have a version of Excel that supports the TOROW function:

Book2
	A	B	C	D	E	F	G	H
1	A1	B1
2	A2	B2
3	A3	B3
4	A4	B4
5
6	A1	B1	A2	B2	A3	B3	A4	B4
Sheet1

Cell Formulas
Range	Formula
A6:H6	A6	=TOROW(A1:B4)
Dynamic array formulas.

Thank you. I tried this formula. It works well to solve the problem. Much appreciated.

 

[frank933]

VBA solution :

Option Explicit

Sub TransposeColumnsAB()
    Dim i As Long
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
   
    For i = 1 To lastRow
        Cells(1, (i - 1) * 2 + 1).Value = Cells(i, 1).Value
        Cells(1, (i - 1) * 2 + 2).Value = Cells(i, 2).Value
    Next i
End Sub

Works great. Thank you Sir.

 

[Logit]

You are welcome

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)