Creating single line record from a Pivot table or even the original data

[Excel_Al]

I get various downloads of data from a legacy system which spits out multiple records which has a unique identifier, location reference and some text upto 180 characters long, every carriage return creates a new record, but the identifier allows me to create a pivot table to allow me to read the record in full, I then do a bit of bodging of the original data to create single unique records to import into a GIS system, but this doesn't allow me to read the record in full as my bodge strips out the text.

I'd like to transpose the additional (upto) 180 characters of text per line for each pivot into an individual column of text; that could entail upto 252 rows of info transposing to 252 columns wide, but is often only 8 rows of text information, unfortunately the number of lines vary randomly and manual manipulation is painfully slow, if I want to retain the text.

i.e.
Unique ID, Postcode, Text1
www-001, EC1a 1BB, 1 quick brown fox
www-001, EC1a 1BB, 2 quick brown fox
www-001, EC1a 1BB, 3 quick brown fox
www-001, EC1a 1BB, 4 quick brown fox

becomes:

Unique ID, Postcode, Text1, Text 2, Text 3, Text 4,
www-001, EC1a 1BB, 1 quick brown fox,, 2 quick brown fox, 3 quick brown fox, 4 quick brown fox

Is it possible to create this in a relatively automated way, I've bodged a recent 50k line file into 5000 unique records, but I'd like to add value to the GIS system by allowing all the relevant text to be read if needs be. Tried to upload an example file, but it wasn't possible to do this for some reason. To add to this I'm trying to do this in Excel 2003 :-)

I was thinking of an IF statement of some sort around the Unique ID and and the Text1 field, then a copy and paste special of the text, but my Excel and thinking skills aren't up to the job of what I want to do given the huge number of records I want to have available to view in the GIS system.

[JeteMc]

Here's a thought.
First concatenate the text strings using the following formula:

Please Login or Register  to view this content.

Next add the ID and Postcode using the following formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Excel_Al]

Really getting towards a good solution and a big thank you, my brain struggles to get around this (voodoo magic ), but I do find it is possible to modify other peoples work to get where I need to, if there aren't too many steps for me to take.

I've added this to some of my data and the concatenate stops at 1050 characters, I could live with this to be honest, given I can only have 256 characters per field in the GIS system any way, but max number of lines I've got is 252, which is a potential 45360 characters

One of my bodges will now be more elegant using

Please Login or Register  to view this content.

Like all these things, I've now got mission creep, I've to try to identify locations were more than 4 dates in 2 months occur, I'm sure it will involve nested IF statements, but I think this might require a bit more thinking about and another thread.