asked on

Making An Excel Row Into Two Rows

I am using Excel 2007. I have a spreadsheet containing many rows. Each rowcontains the columns col1,col2,col3,col4,col5,col6. I want to create two seperate rows for each row. The first row should contain columns col1,col2,col3 and the second row should contain columns col4,col5 and col6. How can I do this in Excel?

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

with your list starting in A1, put this formula in H1

=INDEX($A:$F,CEILING(ROW()/2,1),(MOD(ROW()-1,2)*3)+COLUMN(A1))

copy across to J1 and down. Copy the result and use Paste Special > Values to paste only the results.

cheers, teylyn

ASKER CERTIFIED SOLUTION

Michael Fowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zabac

ASKER

What if I have a total of 10, 20 or some other number of columns and I want the first half (ie columns 1-10) to be in one row and the second half (ie columns 11-20) in the next row, how could the above command be used for for any number of columns?

Michael Fowler

I changed the macro so that now it needs to be called with the column number that the is the start of the columns to be moved so from your first example that is 4 and the column number of the last column to move ie 6 from the first example.
It then inserts these columns under the current data as requested

Michael

Sub MoveData(startCol As Long, endCol As Long)
   Dim currentRow As Long, i As Long, colNumbers As Long
 
    colNumbers = endCol - startCol + 1
    
    i = colNumbers + 1
    While Not IsEmpty(Range("A" & i).Value)
        rows(i & ":" & i + colNumbers - 1).Insert
        i = i + colNumbers * 2
    Wend
      
   For i = 1 To Range("A" & rows.Count).End(xlUp).Row Step 6
      Range(Cells(i, startCol), Cells(i + colNumbers - 1, endCol)).Cut Range("A" & i + colNumbers)
   Next
   
End Sub

Sub test()
   Call MoveData(4, 6)
End Sub

Open in new window

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

In your question it originally said that you wanted to do this without programming. Are you still interested in a formula solution?

Michael Fowler

Here is a slightly more generic version. numUntouchedCols is the number of columns to be left in place. ie 3 in your first example

@teylyn - Maybe I am going mad but I took the original question to mean that a VBA or formula solution would be acceptable

@zabac - If I missed the point here please ensure all the points go to teylyn

Michael

Sub MoveData(numUntouchedCols, startCol As Long, endCol As Long)
   Dim currentRow As Long, i As Long, moveCols As Long
 
    moveCols = endCol - startCol + 1
    
    i = numUntouchedCols + 1
    While Not IsEmpty(Range("A" & i).Value)
        rows(i & ":" & i + moveCols - 1).Insert
        i = i + numUntouchedCols * 2
    Wend
      
   i = 1
   
   For i = 1 To Range("A" & rows.Count).End(xlUp).Row Step endCol
      Range(Cells(i, startCol), Cells(i + moveCols - 1, endCol)).Cut Range("A" & i + moveCols)
   Next
   
End Sub


Sub test()
   Call MoveData(3, 4, 6)
End Sub

Open in new window