We help IT Professionals succeed at work.

Making An Excel Row Into Two Rows

zabac
zabac asked
on
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?
Comment
Watch Question

Most Valuable Expert 2011
Awarded 2010

Commented:
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
Solutions Consultant
Commented:
The attached sub will perform the actions you require.

I have also attached an example file so you can see it in action

Michael
Sub MoveData()
   Dim currentRow As Long, i As Long

    i = 3 + 1
    While Not IsEmpty(Range("A" & i).Value)
        rows(i & ":" & i + 2).Insert
        i = i + 6
    Wend
      
   For i = 1 To Range("A" & rows.Count).End(xlUp).Row Step 6
      Range("D" & i, "F" & i + 2).Cut Range("A" & i + 3)
   Next
   
End Sub

Open in new window

Move-Data-Example.xls

Author

Commented:
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 FowlerSolutions Consultant

Commented:
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

Most Valuable Expert 2011
Awarded 2010

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

Commented:
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