merging text lines

[doylie]

I have been given a problem to solve in access (export in to excel), which involves some vb. This is fine, however I'm more of a beginner in C code but not Vb so any help would be appreciated.

I have to write some code which will merge text from several lines onto one line but keep the line numbers.Here an example of some data:-

Job no.___sub no.__line.no._Text.
123________01________01_____Line of text...................
123________01________02_____Line of text........
123________02________01_____Line of text...........
423________01________01_____Line of text.....................
423________01________02_____Line of text...
423________01________03_____Line of text.................
423________02________01_____Line of text...........
423________03________01_____Line of text..................

As you can see the job no's aren't consecutive, but they could be...
Each job contains X amount of sub no's....
And each sub no. contains X amount of line no's of varying lines of text....

What I want it to look like is this..
123_______01________01. Line of text......
____________________02. Line of text....
123_______02________01. Line of text...........

etc.etc.
Please note underscore is just for illustration!

Any help would be appreciated..

Ps. I am a beginner at this so please give notes with code, also it doesn't matter whether the solution relates to access or excel, as I can import export where neccesary.Also Any pointers on how to run the code in a query.Cheers

 

[simongallop]

As your problem strikes me as being a visual one rather than a data I would suggest that you carry out the code in Excel, so as not to affect the underlying data.

I would sort the data by:Job, Sub, Line to ensure that everything is in the correct order, then presuming that Job is in columnA etc.

Sub Clear_Cells()
Range("A3").Select 'Misses out the header and the first row
Do
If activecell.offset(0,0) = activecell.offset(-1,0) AND activecell.offset(0,1) = activecell.offset(-1,1) Then
activecell.offset(0,0) = ""
activecell.offset(0,1)=""
end if
activecell.offset(1,0).select
loop until activecell.offset(0,0)=""
end sub


hth

 

[doylie]

This gets me a little nearer, however I need the Job ,sub, and lineNo in one row and all the text in one cell, the code you gave me only clears every other cell under job and sub.

 

[simongallop]

Sorry got distracted whilst answering!!

Dim strJob, strSub as string

Sub Clear_Cells()
strJob=Range("A2")
strSub=Range("B2")

Range("A3").Select
Do
If activecell.offset(0,0) = strJob AND activecell.offset(0,1) = strSub Then
activecell.offset(0,0) = ""
activecell.offset(0,1)=""
Else
strJob=activecell.offset(0,0)
strSub=activecell.offset(0,1)
end if
activecell.offset(1,0).select
loop until activecell.offset(0,0)=""
end sub

HTH

 

[doylie]

Attached is a copy of what I'm trying to do.

 

[simongallop]

OK. You will have to work out the formatting but this ought to get the data in the right place!! (to do the formatting record how you want it and then add and amend to the code)

Am still using columns A & B to search off


Dim strJob, strSub, strDesc as string

Sub Clear_Cells()
strJob=Range("A2")
strSub=Range("B2")
strDesc = range("G2") & ") " & range("H2")

Range("A3").Select
Do
If activecell.offset(0,0) = strJob AND activecell.offset(0,1) = strSub Then
activecell.offset(0,0) = ""
activecell.offset(0,1)=""
strDesc = strDesc & chr(10) & activecell.offset(0,6) & ") " & activecell.offset(0,7)
Selection.EntireRow.Delete
Else
activecell.offset(-1,7)=strDesc
strJob=activecell.offset(0,0)
strSub=activecell.offset(0,1)
strDesc = activecell.offset(0,6) & ") " & activecell.offset(0,7)
activecell.offset(1,0).select
end if
loop until activecell.offset(0,0)=""
activecell.offset(-1,7)=strDesc
end sub

HTH

 

[doylie]

Thanks very much, works like a dream.