-
Nov 10th, 2016, 04:18 AM
#1
Thread Starter
New Member
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...
-
Nov 10th, 2016, 05:30 AM
#2
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
-
Nov 10th, 2016, 07:41 AM
#3
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.
-
Nov 11th, 2016, 04:52 AM
#4
Thread Starter
New Member
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.
-
Nov 11th, 2016, 05:31 AM
#5
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)
-
Nov 11th, 2016, 06:09 AM
#6
Thread Starter
New Member
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?
-
Nov 11th, 2016, 07:51 AM
#7
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?
-
Nov 12th, 2016, 05:30 PM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|