Results 1 to 8 of 8

Thread: Prob. asked before: multiple rows into single row?

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2016
    Posts
    8

    Prob. asked before: multiple rows into single row?

    Hello good people of the vbforums.com

    I have a pickle jar of a problem here. I'm sure there is a solution somewhere, somehow but it totally eludes me.

    I have a delimited .txt file that contains data like so:

    ABC^ This
    ABC^ is
    ABC^ a description
    DEF^ And this is
    DEF^ another description
    GHI^ And this
    GHI^ is
    GHI^ a third
    GHI^ description

    As you can see, the first column contains rows with same value, which occurs different amount of times (ABC occurs on 3 rows, DEF on 2 and GHI on 4).

    I need to get this data, somehow, to looking like this instead:

    ABC This is a description
    DEF And this is another description
    GHI And this is a third description

    Is there any simple way of doing this? I tried looking into an old add-in import tool which wouldnt work with my version of Excel. I do know some VB, but its on a very basic level.

    Anyone got any good ideas? Much obliged...

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Prob. asked before: multiple rows into single row?

    Just an untested sample:
    Code:
    Private Sub ParseFile()
      Dim fID As Integer
      Dim sLine As String
      Dim sKey As String, sPrevKey As String
      Dim sText As String
      
      fID = FreeFile
      Open "yourfile" For Input As fID
        Do Until EOF(fID)
          Line Input #fID, sLine
          If Len(sLine) > 0 Then
            sKey = Left$(sLine, 4)
            If sKey <> sPrevKey Then
              If Len(sText) > 0 Then Debug.Print sText
              sText = sLine
              sPrevKey = sKey
            Else
              sText = sText & " " & Mid$(sLine, 5)
            End If
          End If
        Loop
      Close #fID
    
      If Len(sText) > 0 Then Debug.Print sText
    End Sub

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Prob. asked before: multiple rows into single row?

    Tested, and it works. As long as the "key" is 4 characters long, you'd be okay. If the "ABC^" can vary in length, you'd need to split each line, then use the 2nd element of the resulting array as the new line of text.

  4. #4

    Thread Starter
    New Member
    Join Date
    Oct 2016
    Posts
    8

    Re: Prob. asked before: multiple rows into single row?

    Thanks a bunch, lads. However, I cant get it to work :\ Thing is, that I want to, preferably, have the values in the second coloumn joined in a single cell with line breaks, if at all possible.

    Been trying to do it by importing the file into ACCESS and running some smart SQL Query, but this isnt really my field. I've turned my hopes over to the supplier of the source file, hoping they can provide me with a file in a "more correct" formatting.

  5. #5
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Prob. asked before: multiple rows into single row?

    You want line breaks? Then replace the " " by vbCrLf in the following line:
    Code:
    sText = sText & " " & Mid$(sLine, 5)
    Code:
    sText = sText & vbCrLf  & Mid$(sLine, 5)

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2016
    Posts
    8

    Re: Prob. asked before: multiple rows into single row?

    Ive tried running the above code as a VBA-macro in Excel. The code seems to run fine, but I dont get any return/output from it. Maybe Im doing something wrong?

  7. #7
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Prob. asked before: multiple rows into single row?

    It does only print the result in the debug (or immediate window).

    Where should the output go to?

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Prob. asked before: multiple rows into single row?

    Ive tried running the above code as a VBA-macro in Excel
    post the exact code as you tested
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width