Excel 2007 Condense Multiple Rows Into One Row

Posted on 2012-08-15
Last Modified: 2012-08-20

I have a spreadsheet that contains 6 columns with the same heading but different data in each column.  I need to merge it all under one column.  Any suggestions on how to do it?  My data range is A1:F50 with the column headers in row 1.

Question by:Escanaba
    LVL 6

    Expert Comment

    Have you tried using the concatenate command to achieve this? I assume simply using the merge function isn't what you're looking for!
    LVL 1

    Author Comment

    I dont think that will work.  I've attached a sample file showing what Im looking for.  Shee1 shows the before and sheet2 shows the after.
    LVL 1

    Accepted Solution

    Closing question.  I determined the only way to handle this is with VB code.  If anyone runs into a similar issue the following is what I came up with:

    Sub Gather_Data()
        Dim iRow As Integer, iCol As Integer, colCollection As New Collection
        iRow = 2
        iCol = 1
        Do Until Worksheets("Sheet1").Cells(iRow, iCol).Value = ""
            Do Until Worksheets("Sheet1").Cells(iRow, iCol).Value = ""
                colCollection.Add Worksheets("Sheet1").Cells(iRow, iCol).Value
                iRow = iRow + 1
            iRow = 2
            iCol = iCol + 1
        Call Write_Data(colCollection, Worksheets("Sheet1").Range("A1").Value)
    End Sub
    Sub Write_Data(colWrite As Collection, stTitle As String)
        Dim iCount As Integer
        Worksheets("Sheet2").Range("A1").Value = stTitle
        For iCount = 1 To colWrite.Count
            Worksheets("Sheet2").Cells(iCount + 1, 1).Value = colWrite.Item(iCount)
        Next iCount
    End Sub

    Open in new window

    LVL 1

    Author Closing Comment

    Determined VB code was the best solution and generated a viable code that did the trick.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
    Outlook Free & Paid Tools
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now