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?
zabacAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
0
Michael FowlerSolutions ConsultantCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zabacAuthor 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?
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Michael FowlerSolutions ConsultantCommented:
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

0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
In your question it originally said that you wanted to do this without programming. Are you still interested in a formula solution?
0
Michael FowlerSolutions ConsultantCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.