Concatenate Rows...

[Petzl]

Hi,

I have some data I need to import. Is in the format:

Company NoteID LineID Text

Company1 1 0 Text1
Company1 1 1 Text2
Company2 1 0 Text1
Company2 1 1 Text2
Company2 2 0 Text1
Company3 1 0 Text1
Company3 1 0 Text2
Company3 1 0 Text3

The data has been exported from a proprietary format. I need to end up with one line of text per NoteID.

Hopefully I have explained this well enough and it is not too complicated for me to understand ;-)

Thanks,

Petzl

 

[xlbo]

import into where?
data is held where ? in what app ?
I assume you want it broken vy Company as well but you do not say ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

File / Save as / *.csv

Change extension to .txt, open up in notepad and then just replace , with space (Unless of course you want commas, in which case leave as is)

Save and you are done.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

No, you were not very clear.

"...one line of text per NoteID..."

so this is all on line 1?
[tt]
Company1 1 0 Text1 Company1 1 1 Text2 Company2 1 0 Text1 Company2 1 1 Text2 Company3 1 0 Text1 Company3 1 0 Text2 Company3 1 0 Text3
[/tt]
and this is on line 2?
[tt]
Company2 2 0 Text1
[/tt]
???



Skip,

_[red][/red]

 

[KenWright]

Oops - my bad - cheers Skip

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Petzl]

Ok...to try to clear up.....I need to end up with one line of text per NoteID so therefore:

Company1 1 0 Text1 Text2
Company1 1 1
Company2 1 0 Text1Text2
Company2 1 1
Company2 2 0 Text1
Company3 1 0 Text1 Text2 Text3
Company3 1 0
Company3 1 0

I then delete the Company lines with blank in the text column by sorting.....hope this makes more sense now....

Thanks guys!

 

[SkipVought]

That is NOT what you are asking for.

Do you want on row per Company & NoteID WITHOUT regard or inclusion of LineID OR with the MINIMUM LineID.

Would you please verify if and which is correct and please be...

clear, concise & complete in your reequirements.

Skip,

_[red][/red]

 

[xlbo]

still havn't told us which app you are using....assuming excel but could be access....not told us where you are trying to import to. If it is a SQL database, it may be that the data can be queried in without re-formatting...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Petzl]

Ok........data is a csv file from a proprietary DB called SPS and I need to be able to work with it in Excel.

It is currently in the format:
Company NoteID LineID Text
Company1 1 0 Text1
Company1 1 1 Text2
Company2 1 0 Text1
Company2 1 1 Text2
Company2 2 0 Text1
Company3 1 0 Text1
Company3 1 0 Text2
Company3 1 0 Text3

I need it in the format:
Company NoteID LineID Text
Company1 1 0 Text1 Text2
Company1 1 1
Company2 1 0 Text1Text2
Company2 1 1
Company2 2 0 Text1
Company3 1 0 Text1 Text2 Text3
Company3 1 0
Company3 1 0

NoteID refers to the occurrence of a note. If is says 1 then that refers to note 1. If the line ID says 0 then that refers to the first line of that note.If there is more that one line to the note then the line ID will say 2, 3 etc. So Company 3 has 1 Note with 3 lines.

I hope this is more clear......

Thanks,

Petzl

 

[Petzl]

No more ideas???

 

[SkipVought]

Not without VBA code.

Skip,

_[red][/red]

 

[Petzl]

Well VBA Code was exactly what I needed and another forum understood straight away. If anyone was interested....

Sub sample()
Dim i As Long
Application.ScreenUpdating = False
For i = Range("a" & Rows.Count).End(xlUp).Row To 2 Step -1
If Cells(i, "a").Value = Cells(i + 1, "a").Value And Cells(i, "b").Value = Cells(i + 1, "b").Value Then
Cells(i, "d").Value = Cells(i, "d").Value & " " & Cells(i + 1, "d").Value
Rows(i + 1).Delete
End If
Next
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

This is NOT a code forum. That's VBA Visual Basic for Applications (Microsoft) forum707.

This is the MS Office forum that focuses on non-code solutions.

Glad that you found a solution.



Skip,

_[red][/red]