Need help in converting from rows to columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2618002%22%20slang%3D%22en-US%22%3ENeed%20help%20in%20converting%20from%20rows%20to%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2618002%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20set%20of%20large%20data%20in%20following%20manner%3A%3C%2FP%3E%3CP%3EA%3C%2FP%3E%3CP%3EA.1%3C%2FP%3E%3CP%3EA.2%3C%2FP%3E%3CP%3EA.3%3C%2FP%3E%3CP%3EA.4%3C%2FP%3E%3CP%3EA.5%3C%2FP%3E%3CP%3E(one%2Fcouple%20of%20blank%20rows%20as%20separator)%3C%2FP%3E%3CP%3EB%3C%2FP%3E%3CP%3EB.1%3C%2FP%3E%3CP%3EB.2%3C%2FP%3E%3CP%3EB.3%3C%2FP%3E%3CP%3EB.4%3C%2FP%3E%3CP%3EB.5%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20convert%20it%20to%3A%3C%2FP%3E%3CP%3EA%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20A.1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20A.2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BA.3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BA.4%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20A.5%3C%2FP%3E%3CP%3EB%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB.1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB.2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20B.3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB.4%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB.5%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2618002%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2618240%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20converting%20from%20rows%20to%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2618240%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1121835%22%20target%3D%22_blank%22%3E%40onir0110%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20open%20to%20a%20VBA%20solution%2C%20please%20place%20the%20following%20code%20on%20a%20Standard%20Module%20like%20Module1.%3C%2FP%3E%3CP%3EIn%20the%20attached%2C%20click%20on%20the%20%3CSTRONG%3ETranspose%20Data%3C%2FSTRONG%3E%20button%20to%20get%20the%20desired%20output.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20TransposeData()%0ADim%20lr%20As%20Long%0ADim%20rngArea%20As%20Range%0ADim%20Rng%20%20%20%20%20As%20Range%0ADim%20destCol%20As%20Long%0ADim%20r%20%20%20%20%20%20%20As%20Long%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%0Alr%20%3D%20Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%0AdestCol%20%3D%205%0A%0AOn%20Error%20Resume%20Next%0ASet%20rngArea%20%3D%20Range(%22A2%3AA%22%20%26amp%3B%20lr).SpecialCells(xlCellTypeConstants%2C%203)%0AOn%20Error%20GoTo%200%0ACells(1%2C%20destCol).CurrentRegion.Offset(1).Clear%0Ar%20%3D%202%0AIf%20Not%20rngArea%20Is%20Nothing%20Then%0A%20%20%20%20For%20Each%20Rng%20In%20rngArea.Areas%0A%20%20%20%20%20%20%20%20Rng.Copy%0A%20%20%20%20%20%20%20%20Cells(r%2C%20destCol).PasteSpecial%20xlPasteAll%2C%20Transpose%3A%3DTrue%0A%20%20%20%20%20%20%20%20r%20%3D%20r%20%2B%201%0A%20%20%20%20%20%20%20%20Application.CutCopyMode%20%3D%200%0A%20%20%20%20Next%20Rng%0AEnd%20If%0ACells(2%2C%20destCol).Select%0AApplication.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2622828%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20converting%20from%20rows%20to%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2622828%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20response%2C%20I%20really%20appreciate.%20But%20unfortunately%20I%20don't%20know%20how%20to%20use%20VBA%2C%20hence%20looking%20for%20some%20alternatives%20for%20my%20use%20case.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a set of large data in following manner:

A

A.1

A.2

A.3

A.4

A.5

(one/couple of blank rows as separator)

B

B.1

B.2

B.3

B.4

B.5

 

I want to convert it to:

A      A.1      A.2       A.3       A.4        A.5

B       B.1     B.2        B.3       B.4         B.5

2 Replies

@onir0110 

 

If you are open to a VBA solution, please place the following code on a Standard Module like Module1.

In the attached, click on the Transpose Data button to get the desired output.

 

Sub TransposeData()
Dim lr As Long
Dim rngArea As Range
Dim Rng     As Range
Dim destCol As Long
Dim r       As Long

Application.ScreenUpdating = False

lr = Cells(Rows.Count, "A").End(xlUp).Row
destCol = 5

On Error Resume Next
Set rngArea = Range("A2:A" & lr).SpecialCells(xlCellTypeConstants, 3)
On Error GoTo 0
Cells(1, destCol).CurrentRegion.Offset(1).Clear
r = 2
If Not rngArea Is Nothing Then
    For Each Rng In rngArea.Areas
        Rng.Copy
        Cells(r, destCol).PasteSpecial xlPasteAll, Transpose:=True
        r = r + 1
        Application.CutCopyMode = 0
    Next Rng
End If
Cells(2, destCol).Select
Application.ScreenUpdating = True
End Sub

 

@Subodh_Tiwari_sktneer Thank you for your response, I really appreciate. But unfortunately I don't know how to use VBA, hence looking for some alternatives for my use case.