Group rows together

[LaurenR]

Hello!

This is my second time posting- thank you always for all of your help! Here is what I'm trying to do.

I have a 2000 page Word document that I need to sort through. My initial solution was to copy and paste it into Excel (see attached "before" file for a section of the copied results). As you can see, it copies all of the info into Column A.

I then separated the data into separate columns, and deleted those that were not important to me. This is where I get stuck. I only need the groups of information that correspond to the "Z- New Constr." entry in the new Column A (See the attached "during" file for an example of where I get stuck). The groups are between dashed lines- that comes with the copy from Word.

Is there any way to get to my "after" file without going through and manually deleting all non-New Construction entries? I tried to sort by Column A and it obviously doesn't move all of the corresponding rows.

This file only starts with 6 entries- my real file has many more than that! Any help you can provide would be really appreciated!

Thanks so much!

Lauren

[seckert]

See attached.

Copy the formulas in new colums D and E down for your entire data set.
Copy / Paste Values for these two columns to hard-code the results
Sort on column E
Delete the rows you don't want
Sort on column D to get the remaining data back to it's original order.

Hope this helps

SAE

[protonLeah]

A macro for your consideration:

Option Explicit
Option Base 1
Sub getZ()
    Dim RAWDATA             As Worksheet, _
        PARSEDDATA          As Worksheet, _
        TestLine            As Range, _
        DataBlock           As Range, _
        DataLine            As Range, _
        DataList            As Range, _
        LastUsedRow         As Long, _
        DestRow             As Long, _
        StartPos(3)         As Long, _
        NumChars(3)         As Long, _
        ndx                 As Long
        
    Set RAWDATA = Sheets("sheet1")
    Set PARSEDDATA = Sheets("sheet2")
        
    LastUsedRow = RAWDATA.Cells(Rows.Count, "A").End(xlUp).Row
    Set DataList = RAWDATA.Range("A1:A" & LastUsedRow)
    
    StartPos(1) = 1: NumChars(1) = 26
    StartPos(2) = 27: NumChars(2) = 40
    StartPos(3) = 68: NumChars(3) = 31
    
    For Each TestLine In DataList
        If UCase(Left(TestLine.Value, 2)) = "Z-" Then
        
            Set DataBlock = RAWDATA.Range("A" & TestLine.Row - 1 & ":A" & TestLine.Row + 4)
            
            For Each DataLine In DataBlock
                DestRow = DestRow + 1
                
                With PARSEDDATA.Range("A" & DestRow)
                    For ndx = 1 To 3
                        .Offset(0, ndx - 1).Value = Mid(DataLine.Value, StartPos(ndx), NumChars(ndx))
                    Next ndx
                End With
                
            Next DataLine
            
            PARSEDDATA.Range("c" & DestRow).Value = Left(PARSEDDATA.Range("c" & DestRow).Value, 2)
            
            'put in some dashes to separate records
            For ndx = 1 To 3
                PARSEDDATA.Cells(DestRow + 1, ndx) = String(25, "-")
            Next ndx
            
            'move down to the next blank row
            DestRow = DestRow + 1
            
        End If
    Next TestLine
End Sub

[stanleydgromjr]

LaurenR,

See the attached workbook "Group rows together - LaurenR - VP - SDG11.xls" with macro "MoveData".

Detach and open the attached workbook and run the "MoveData" macro.