Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pull apart a cell (2003 sp2)

    I dumped the attached file from an Access database into Excel. Not sure which medium will be better for what I need to do. I need to separate the updates done in the history field by the time/datestamp and person who did them and create a new record for each one.
    History field example: Line 15 - ID # 13104
    PMT assigned:: Old - PMT New - IET

    Next Activity Time: Old - 01/03/2007 19:00 New - 01/04/2007 09:00
    (~~ Joan Araujo -- 1/3/2007 7:04:20 PM~~)

    Next Activity Time: Old - 01/04/2007 09:00 New - 01/04/2007 10:00
    (~~ Jamie Campos -- 1/4/2007 9:15:07 AM~~)

    Status: Old - OPEN New - CLOSED
    (~~ Jamie Campos -- 1/4/2007 1:03:50 PM~~)

    The (~~ name-- date tiime ~~) entry shows when each new update was done and by whom. What I need to do is take the entire row and create a new record for each new update in the history field. I have no clue how to even begin. Can anyone help? I will most likely have to do this for awhile because there's no chance the original input code will be changed any time soon.
    Attached Files Attached Files

  2. #2
    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: Pull apart a cell (2003 sp2)

    I don't completely understand.

    I presume "line 15" (as will most of the lins) should become multiple lines, what are the lines you want to appear if line 15 is expanded? Could you attach some examples of the before and after using the first few lines of your file?

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull apart a cell (2003 sp2)

    The number of new rows to be created is dictated by the number of history updates. Each update is delineated by (~~ name date time ~~), so if a record had 4 of these elements, I'd need 4 new records, each with one history update. When it's complete, I'd want a list of just the new records created without the original record that had all the history updates. Hope this is plainer. Please see attached. thx
    Attached Files Attached Files

  4. #4
    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: Pull apart a cell (2003 sp2)

    <P ID="edit" class=small>(Edited by sdckapr on 14-Sep-07 19:23. Added PS about CR and LF)</P>Does this do what you want?
    It creates a new sheet with the data parsed, without affecting the original...

    Select the "Results" sheet and then run the code...
    Steve

    <pre>Option Explicit
    Sub WBResultsParser()
    Dim sEnd As String
    Dim iLen As Integer
    Dim iEnd As Integer
    Dim sTemp As String
    Dim iCount As Integer
    Dim i As Integer
    Dim wksNew As Worksheet
    Dim wksSource As Worksheet
    Dim lRow As Long
    Dim lRowNew As Long
    Dim lRows As Long
    Dim iCol As Integer
    Dim iCols As Integer
    Dim AWF As WorksheetFunction

    On Error GoTo ErrRoutine
    Application.ScreenUpdating = False

    sEnd = "~~)"
    Set wksSource = ActiveSheet
    Set wksNew = Worksheets.Add

    Set AWF = Application.WorksheetFunction
    iLen = Len(sEnd)
    iCols = 6
    lRow = 1
    lRowNew = 1

    With wksSource
    lRows = .Range("A65536").End(xlUp).Row
    For iCol = 1 To iCols
    wksNew.Columns(iCol).ColumnWidth = .Columns(iCol).ColumnWidth
    wksNew.Cells(lRowNew, iCol) = .Cells(lRow, iCol)
    Next
    For lRow = 2 To lRows
    sTemp = .Cells(lRow, iCols)
    If Right(sTemp, iLen) <> sEnd Then
    sTemp = sTemp & sEnd
    End If
    iCount = (Len(sTemp) - _
    Len(AWF.Substitute(sTemp, sEnd, ""))) / iLen
    For i = 1 To iCount
    lRowNew = lRowNew + 1
    For iCol = 1 To iCols - 1
    wksNew.Cells(lRowNew, iCol) = .Cells(lRow, iCol)
    Next
    iEnd = InStr(sTemp, sEnd) + iLen - 1
    wksNew.Cells(lRowNew, iCols) = Left(sTemp, iEnd)
    sTemp = Mid(sTemp, iEnd + 1)
    Next
    Next
    End With
    MsgBox "DONE"
    ExitRoutine:
    Set wksSource = Nothing
    Set wksNew = Nothing
    Set AWF = Nothing
    Application.ScreenUpdating = True
    Exit Sub
    ErrRoutine:
    MsgBox Err.Description, vbExclamation
    Resume ExitRoutine
    End Sub</pre>


    PS. BTW. I left the Carriage returns and line feeds in the cells since your example kept them in. The linefeeds will (of text wrapping is on) cause the cells to wrap at that point. The carriage returns do nothing except display an "unprintable character" in the cell. If the text wrapping is off, the linefeed will also display as an "unprintable character". The could be removed with the SUBSTTITUTE function if desired:

    <pre> sTemp = .Cells(lRow, iCols)
    <font color=red> sTemp = AWF.Substitute(sTemp, vbCR, "")
    sTemp = AWF.Substitute(sTemp, vbLF, "") </font color=red>
    If Right(sTemp, iLen) <> sEnd Then</pre>


  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull apart a cell (2003 sp2)

    Sorry to be so long replying. I was out of the office for several days, then had trouble getting on the board yesterday. Don't want you to think I don't appreciate all your efforts. All I can say is WOW! You're awesome! This is exactly what I need. Hopefully I'll eventually be able to pick apart the code so I can not only learn from it, but apply it to future situations. Thanks again.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull apart a cell (2003 sp2)

    Steve - I hate to callon you yet again, but now I'm told the first line in the history that contains the Opened by field with the name of the opener has to be split from the rest of the history updates. That it needs to be the first new row created for each different source row. After that is would remain the same. Is there a way to incorporate that without too much hassel?

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull apart a cell (2003 sp2)

    Kill me now!!! I should have known there'd be more! Can you possibly adjust your code to do the same as the last post with one difference? The difference is the source sheet. I want to use the original source sheet that has quite a few more fields; but I still only want to pull the ones we currently show on the ouput sheet. I'm afraid there will be more requests down the road so I'm hoping I can study your code and do them (or at least most of it) myself. Attached is a short example of the source sheet that shows all fields.
    Attached Files Attached Files

  8. #8
    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: Pull apart a cell (2003 sp2)

    Patti,
    I apologize, but i have been out of town without my computer with limited time to work on projects. I made a little time to try and catchup with open posts on the hotel computer but do not have time to work on this. I will try and get to it early next week as time permits if no one else responds by then.

    Steve

  9. #9
    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: Pull apart a cell (2003 sp2)

    How does this work?

    The code logic is the same. The main differences is the columns extracted. Instead of using cols 1-5 as in your sample, i defined the cols to extract explicitly and put them into an array and then looped throught this array. If the cols will vary it is possible to create a list of the cols names and have the code at runtime determine what col numbers those names are in, but I presumed that the column outputs would remain the same.

    You can compare the 2 codes to see the new and modified lines (I leave that to you to discover). if you have particular questions on the logic I will be happy to anwer them. Stepping through the code (<F8>) can let you see what the code is doing at each loop.

    Steve

    <pre>Option Explicit
    Sub WBResultsParser()
    Dim vCols As Variant
    Dim iIndex As Integer
    Dim sEnd As String
    Dim iLen As Integer
    Dim iEnd As Integer
    Dim sTemp As String
    Dim iCount As Integer
    Dim i As Integer
    Dim wksNew As Worksheet
    Dim wksSource As Worksheet
    Dim lRow As Long
    Dim lRowNew As Long
    Dim lRows As Long
    Dim iCol As Integer
    Dim iCols As Integer
    Dim AWF As WorksheetFunction

    On Error GoTo ErrRoutine
    Application.ScreenUpdating = False

    vCols = Array(1, 3, 5, 11, 13, 15)
    sEnd = "~~)"
    Set wksSource = ActiveSheet
    Set wksNew = Worksheets.Add

    Set AWF = Application.WorksheetFunction
    iLen = Len(sEnd)
    iCols = vCols(UBound(vCols))
    lRow = 1
    lRowNew = 1

    With wksSource
    lRows = .Range("A65536").End(xlUp).Row
    For iIndex = LBound(vCols) + 1 To UBound(vCols) + 1
    iCol = vCols(iIndex - 1)
    wksNew.Columns(iIndex).ColumnWidth = .Columns(iCol).ColumnWidth
    wksNew.Cells(lRowNew, iIndex) = .Cells(lRow, iCol)
    Next
    For lRow = 2 To lRows
    sTemp = .Cells(lRow, iCols)
    If Right(sTemp, iLen) <> sEnd Then
    sTemp = sTemp & sEnd
    End If
    iCount = (Len(sTemp) - _
    Len(AWF.Substitute(sTemp, sEnd, ""))) / iLen
    For i = 1 To iCount
    lRowNew = lRowNew + 1
    For iIndex = LBound(vCols) + 1 To UBound(vCols)
    iCol = vCols(iIndex - 1)
    wksNew.Cells(lRowNew, iIndex) = .Cells(lRow, iCol)
    Next
    iEnd = InStr(sTemp, sEnd) + iLen - 1
    wksNew.Cells(lRowNew, iIndex) = Left(sTemp, iEnd)
    sTemp = Mid(sTemp, iEnd + 1)
    Next
    Next
    End With
    MsgBox "DONE"
    ExitRoutine:
    Set wksSource = Nothing
    Set wksNew = Nothing
    Set AWF = Nothing
    Application.ScreenUpdating = True
    Exit Sub
    ErrRoutine:
    MsgBox Err.Description, vbExclamation
    Resume ExitRoutine
    End Sub</pre>


  10. #10
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull apart a cell (2003 sp2)

    Can't believe you apologized for not having a lot of time to spend on my problem! I'm already extremely grateful for what you've done. I am getting some unexpected results though. when it begins to write the new rows: wksNew.Cells(1RowNew, iIndex) = .Cells(1Row, iCol)
    it writes the history in each field instead of the ID #, Case #, etc. I attached what I got and what it should be. Thanks so much again for all your hard work! I'll never be at your level, but I do keep learning and slowly am able to do more myself.
    Attached Files Attached Files

  11. #11
    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: Pull apart a cell (2003 sp2)

    I don't get unexpected results. The modified code in the attached breaks at the CR/LF after the first entry and then does the "~~)"

    Does this work better?

    Steve
    Attached Files Attached Files

  12. #12
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull apart a cell (2003 sp2)

    Steve - what you sent works perfectly. Please be assured your hard work won't be wasted. I plan to spend a good bit of time stepping through this in order to understand it better. I have the general idea, but it's still very complicated for me. I really do want to learn to do alot more of this stuff myself or at least get to the poiint where I can do most of it but just need a bit of help at times. Thanks for your time and especially your patience.

  13. #13
    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: Pull apart a cell (2003 sp2)

    You are very welcome. I am glad I could help

    Steve

Posting Permissions

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