Data from rows into columns

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I'm not that great in Excel so I was wondering if there is a way to separate rows and move to different columns in Excel 2007/2010.

I have the huge list data in one column as shown below:
ABC
France
20
FXG
DEF
Germany
GO
15
ABC
INDIA
OXY
20
GHI
LONDON
MUST
27

I wanted the data in this format "ABC France 20 FXG" in four separate columns. Since the data is huge and manually copy pasting using transpose options takes time. Can someone please help me?

Many thanks in advance for looking and solving this!

Regards,
Cheryl

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
how one can identify that what cells belong together?
Rizvisa1, T
Thanks for picking this up and apology for being so confusing here...the first 4 data/rows is in Column A in this format as mentioned below
ABC
France
20
FXG
And I want a formula where I can pull up all the four rows in one cell in te following format "ABC France 20 FXG" hope this make sense?
Hello, Is there anyone who can help me out with this?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
try this. Correct the sheet name, column etc in the code

Public Sub copyNRowsToColumns()

    Dim sSrcSheet As String
    Dim lSrcRowStart As Long
    Dim iSrcCol As Integer
    
    Dim sTgtSheet As String
    Dim lTgtRowStart As Long
    Dim iTgtCol As Integer
    Dim lRowsToTranspose As Long
    
    Dim lSrcRow As Long
    Dim lTgtRow As Long
    Dim lLastSrcRow As Long
    
    sSrcSheet = "Sheet1"
    lSrcRowStart = 1
    iSrcCol = 1
    
    sTgtSheet = "Sheet1"
    lTgtRowStart = 1
    iTgtCol = 2
    lRowsToTranspose = 4
    
    With Sheets(sSrcSheet)
        lLastSrcRow = .Cells(.Rows.Count, iSrcCol).End(xlUp).Row
        
        For lSrcRow = lSrcRowStart To lLastSrcRow Step lRowsToTranspose
            .Range(.Cells(lSrcRow, iSrcCol), .Cells(lSrcRow + lRowsToTranspose - 1, iSrcCol)).Copy
            Sheets(sTgtSheet).Cells(lTgtRowStart, iTgtCol).PasteSpecial Transpose:=True
            lTgtRowStart = lTgtRowStart + 1
        Next lSrcRow
    End With
End Sub