Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
 _____________|__________|__________________________|___
share|improve this question
    
The example you've given is the "wrong" layout for the data? What exactly is the intended "correct" layout you're aiming for? –  DMA57361 Sep 21 '10 at 7:52
    
all 3 values for Employment History should be within the same column of row 1, so that one person occupies one row, just as they would in a real database –  Andrew Heath Sep 21 '10 at 7:56
    
If the first column is empty for all duplicates I think a simple VBA macro will probably be the best way to do this for you - I'll throw something together and stick it in an answer. –  DMA57361 Sep 21 '10 at 7:58
    
if it helps you, DMA57261, column A is always merged to the appropriate size, it's columns further along the dataset that are sporadically broken. –  Andrew Heath Sep 21 '10 at 8:01
    
So "A1" is actually over "A1:A3" in the inital example? –  DMA57361 Sep 21 '10 at 8:02

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.