We help IT Professionals succeed at work.

Excel 2007 Condense Multiple Rows Into One Row

Hello,

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.

Thanks!
Comment
Watch Question

Commented:
Have you tried using the concatenate command to achieve this? I assume simply using the merge function isn't what you're looking for!
EscanabaManager - HR Analytics

Author

Commented:
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.
EE-Sample.xlsx
Manager - HR Analytics
Commented:
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
        Loop
        iRow = 2
        iCol = iCol + 1
    Loop
    
    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").Cells.ClearContents
    
    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

EscanabaManager - HR Analytics

Author

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.