Thread: How to merge cells, with out loosing text

Hi

I have a set of data, which has a bad format for the analysis I'm going to make of it.
It's made up of cases, which is indicated in the first columns, then there's a lot of keywords in the third column, but separated in rows, the same thing goes with the last column, which is a text; i.e. description of the case.
Can I some how merge the row, that these keyword, and the text is now split into?

Here an example of how the sheet looks like

Since we are not sure how you are going to analyze this data, I suggest that you provide an ouptut sheet that shows how you want the data to look so that you can analyze it.

Do you really want to merge the cells or would you prefer to concatenate the strings in columns D and E together? Also, is the merge occurring on breaks in either column a, B, or C, or do they always have the same values?

well, that I'm not sure.

The data is cases which has been reviewed by a juridical counsel (that's also why I've "xxx/yyy.etc'ed" it).

Column A contains the date of the specific case
Column B contains no important information
Column C contains the result of the deliberation in the counsel
Column D contains keyword applying to the case
Column E text of varying length, a sum up of the case.

I have another worksheet with the keywords generated as a case X variable matrix. So they aren't that important either.

What I need is to match the text/sum-up to the specific case in the mentioned worksheet. That is, I thought I merge the text-pieces from each case into one cell, to export it to my matrix.

Here's another workbook with three sheets; the last one is the final result I'd like to end up with. Maybe I should have made it this way from the beginning, but.... Here it is:

Try running this macros and see how close we are. It will create a new worksheet called Result each time it runs, so you will need to delete it if you run it more than once.

Option Explicit

' merge the data in columns D and E based on nonblank case
' numbers in column A
Sub MergeKeywordsandText()

    Dim theKeywords As String
    Dim theText As String

    ' create a new worksheet to store the results
    Dim theSheet As Worksheet
    Set theSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    theSheet.Name = "Result"

    ' copy the header
    Worksheets(1).Range("A1:E1").Copy theSheet.Range("A1")

    Dim theNextRow As Long    ' the next row to write to the output sheet
    theNextRow = 2

    Dim rw As Range

    For Each rw In Worksheets(1).UsedRange.Rows

        ' skip row one
        If (rw.Row = 1) Then

            ' pick up the first case, and other data
        ElseIf (rw.Row = 2) Then
            theSheet.Cells(theNextRow, 1).Value = rw.Cells(1, 1).Value
            theSheet.Cells(theNextRow, 2).Value = rw.Cells(1, 2).Value
            theSheet.Cells(theNextRow, 3).Value = rw.Cells(1, 3).Value
            theKeywords = rw.Cells(1, 4).Value
            theText = rw.Cells(1, 5).Value

            ' this is the same case a new case number
        ElseIf (rw.Cells(1, 1).Value = "") Then
            theKeywords = theKeywords & " " & rw.Cells(1, 4).Value
            theText = theText & " " & rw.Cells(1, 5).Value

            ' the case number has changed
        Else
            theSheet.Cells(theNextRow, 4).Value = theKeywords
            theSheet.Cells(theNextRow, 5).Value = theText
            theKeywords = rw.Cells(1, 4).Value
            theText = rw.Cells(1, 5).Value
            theNextRow = theNextRow + 1
            theSheet.Cells(theNextRow, 1).Value = rw.Cells(1, 1).Value
            theSheet.Cells(theNextRow, 2).Value = rw.Cells(1, 2).Value
            theSheet.Cells(theNextRow, 3).Value = rw.Cells(1, 3).Value
        End If
    Next rw

    ' output the last case
    theSheet.Cells(theNextRow, 4).Value = theKeywords
    theSheet.Cells(theNextRow, 5).Value = theText
    
    ' set the column widths, alignment, and wrap text
    theSheet.Range("A:E").VerticalAlignment = xlVAlignTop
    theSheet.Columns(4).ColumnWidth = 27
    theSheet.Columns(5).ColumnWidth = 88
    theSheet.Columns(4).WrapText = True
    theSheet.Columns(5).WrapText = True
    

End Sub

Happy analyzing