Merging cells in Excel 2007 because the web exporter is a punk

Question

I have to use a web site to export data in XLS format, to be converted to CSV, to be dumped into a MySQL database.

My problem is that this POS exporter treats carriage returns in the original data as new fields, so that the Excel file is a mess:

Example:

[Name]          [Age]     [Employment History]     // Excel File:
 Smith, John     30        2005-2006 Bennigan's    // row 1
                           2006-2007 Shenanigan's  // row 2
                           2007-2008 Grizzlebee's  // row 3
 Smith, Karen . . .                                // row 4

This totally effs my one-step CSV conversion. The standard merge command is not helpful because it drops all the data apart from the first field.

The only solution I've found is that I can insert an adjacent column, merge the appropriate rows, concatenate those 3 lines into the post-merge block in the second column, and then remove the original. This is brutally, almost impossibly time-intensive though.

Does anyone have a better way to solve the problem? The POS web exporter is controlled by another company and completely out of my hands...

Edit to add a "correct" example:

    [Name]    |  [Age]   |  [Employment History]    | // Excel File:
______________|__________|__________________________|__
 Smith, John  |   30     |   2005-2006 Bennigan's   | // row 1
              |          |   2006-2007 Shenanigan's | 
              |          |   2007-2008 Grizzlebee's | 
______________|__________|__________________________|___
 Smith, Karen |   25     |   2001-2010 Ma Bell      | // row 2
 _____________|__________|__________________________|___

Answer 1

As per our discussion in the comments, here's a macro which I think should achieve what you are after. Obviously test it on copied data first in case something bad happens.

Hopefully the only thing you'll need to adjust is the colToMerge constant near the top, to match whichever column your data to merge happens to be in.

Edit

I've just spotted Excel 2007 in your question title, so please note that I wrote this in 2003.
Hopefully it should still work because it's only doing very basic movement and cell value manipulation. Let me know if it doesn't, and I'll adjust it for Excel 2007 once I'm home this evening.

---

Code

Sub MergeEmploymentCol()

    'PARAMETER: The column number you are merging. A=0,B=1,etc
    Const colToMerge = 2

    'First unmerge everything
    Cells.UnMerge

    'Select First "valid" row, done manually incase top rows are packed together
    Range("A1").Select
    Do While ActiveCell.Value <> 0
        ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.Offset(-1, 0).Select

    'Some variables        
    Dim thisRow As Integer, nextRow As Integer, i As Integer

    Do While ActiveCell.Value <> 0
        'Only need to merge if next row is blank
        If (ActiveCell.Offset(1, 0).Value = 0) Then

            'Get the row numbers for this valid row and the next valid row
            thisRow = ActiveCell.Row

            Selection.End(xlDown).Select
            If (ActiveCell.Value <> 0) Then
                nextRow = ActiveCell.Row
            Else
                'must be last row - no valid rows below this one
                'special handling to find correct row
                ActiveCell.Offset(0, colToMerge).Select
                Selection.End(xlUp).Select
                nextRow = ActiveCell.Row + 1
                ActiveCell.Offset(0, -colToMerge).Select

                'Exit if this last valid row has no duplicates
                If (thisRow + 1 = nextRow) Then
                    Exit For
                End If
            End If
            Selection.End(xlUp).Select

            'Merge the data between here and the next valid row
            Dim combinedData As String
            combinedData = ""
            For i = 0 To (nextRow - thisRow) - 1
                combinedData = combinedData & ActiveCell.Offset(i, colToMerge).Value & Chr(10)
            Next i

            'Trim last (unnecessary) line-return
            combinedData = Mid(combinedData, 1, Len(combinedData) - 1)

            'Place data in cell
            ActiveCell.Offset(0, colToMerge).Value = combinedData

            'Delete unneeded rows
            ActiveCell.Offset(1, 0).Rows("1:" & nextRow - thisRow - 1).EntireRow.Select
            ActiveCell.Offset(0, 0).Range("A1").Activate
            Selection.Delete Shift:=xlUp

            'Select first cell of next valid row
            ActiveCell.Offset(0, 0).Select
        Else
            'Next row not blank, so no need to merge
            'Simple step down to next row
            ActiveCell.Offset(1, 0).Select
        End If

    Loop

End Sub