Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Condensing Multi-Row Records (XL2003 SP1)

    I
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condensing Multi-Row Records (XL2003 SP1)

    It shouldn't be too hard to create a macro. Is there a column that can be used to determine where a new record begins, i.e. a column in which the cell in the first row of a record is ALWAYS filled (non-blank), and the cells in the subsequent rows for the same record are ALWAYS blank? Or is there another way to determine which rows belong together?
    Perhaps you could attach a small sample workbook with dummy data.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Condensing Multi-Row Records (XL2003 SP1)

    Thanks for your response Hans.

    In normal circumstances, every record has an entry in colimn A, B and E, but sometimes it isn't on the first row in the record, as you can see from the Location 3 record, (I told you it was poorly designed!). The record divider is ostensibly the row with the lower border, but I'm not sure that can be used in a macro. The Comments column at the end is a complication, too. That's sometimes empty or, for orders that haven't been straightforward, the number of comments entrered can define the number of rows in the record.

    Thamks again for your help.
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condensing Multi-Row Records (XL2003 SP1)

    Jeez...

    I will see if I can come up with something, but it'll take a bit of time. Perhaps someone else will post a readymade solution in the meantime.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Condensing Multi-Row Records (XL2003 SP1)

    What output do you want at the end?

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Condensing Multi-Row Records (XL2003 SP1)

    The output I'm looking for will be an Excel worksheet with the same set of columns, but with a single row per record in the original, with the multi-Row fields appearing in a single cell on each row, with text set to wrap and/or line feed characters preserving the structure currently obtained by splitting the record over several rows. Thus, the address cell for the first record, (Location 1), will consist of four lines, each, (apart from the last), terminated by Char(10) line-feed character.

    This will enable searching and filtering to work. I hope to send whatever macro we end up with to the supplier of the sheet to try to persuade him to use it before it's distributed. I can't be the only one frustrated by the thing!

    Thanks very much for you input.
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condensing Multi-Row Records (XL2003 SP1)

    Try this code (make a copy of your spreadsheet first!)

    Sub Condense()
    ' Declarations
    Dim lngRow As Long
    Dim lngCurRow As Long
    Dim lngMaxRow As Long
    Dim lngTempRow As Long
    Dim lngCol As Long
    Dim lngMaxCol As Long
    Const lngMinRow As Long = 5

    ' Determine last row and column
    With ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
    lngMaxRow = .Row
    lngMaxCol = .Column
    End With

    ' Loop backwards
    lngRow = lngMaxRow
    Do While lngRow > lngMinRow
    lngCurRow = lngRow
    ' Find cell with top border
    Do While Cells(lngCurRow, 1).Borders(xlEdgeTop).LineStyle = xlLineStyleNone
    lngCurRow = lngCurRow - 1
    Loop
    ' Concatenate values
    For lngCol = 1 To lngMaxCol
    For lngTempRow = lngCurRow + 1 To lngRow
    If Cells(lngTempRow, lngCol) <> "" Then
    If Cells(lngCurRow, lngCol) <> "" Then
    Cells(lngCurRow, lngCol) = _
    Cells(lngCurRow, lngCol) & Chr(10)
    End If
    Cells(lngCurRow, lngCol) = _
    Cells(lngCurRow, lngCol) & Cells(lngTempRow, lngCol)
    End If
    Next lngTempRow
    Next lngCol
    ' Delete superfluous rows
    If lngRow > lngCurRow Then
    Range((lngCurRow + 1) & ":" & lngRow).Delete
    End If
    lngRow = lngCurRow - 1
    Loop

    ' Set vertical alignment to top
    Cells(lngMinRow, 1).CurrentRegion.VerticalAlignment = xlVAlignTop
    End Sub

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Condensing Multi-Row Records (XL2003 SP1)

    Hans,

    I've tried your code on several of the archived worksheets we have and it appears to be just the ticket. Many, many thanks for such a fast turnaround. I just need to spend a little time in the VBA Editor screen stepping through the procedure so I can work out exactly how you've achieved it.

    Thank you once again. I really appreciate the help. This place continues to restore my faith in human nature!
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •