Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 792
  • Last Modified:

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!
0
Escanaba
Asked:
Escanaba
  • 3
1 Solution
 
arrorynCommented:
Have you tried using the concatenate command to achieve this? I assume simply using the merge function isn't what you're looking for!
0
 
EscanabaAuthor 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
0
 
EscanabaAuthor 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

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now