Data

[themrg]

Hi Guys,

I hope you can help me. I received some data in the following format:
Mr|A| Jones| $500| 15 Sunset Road| Claremont|||
15/3/2010|Invoice|$2000|
20/3/2010|Payment|$1500|
Mr|G| Moses| $2000| 20 Musket Road| Bellville|||
15/3/2010|Invoice|$4000|
30/3/2010|Payment|$2000|

I need change this raw data so that all the information appears in one string or row. eg:
Mr|A| Jones| $500| 15 Sunset Road|Claremont|15//2010|Invoice|$2000|20/3/2010|Payment|$1500|
I've already been able to split the information into two tables 1 with client data only (Mr|A| Jones| $500| 15 Sunset Road| Claremont|) and the other with transaction data only (15/3/2010|Invoice|$2000|)

I have unique numbers that could link the transaction to the customer (so all Mr Jones' transactions have 1 as a reference while all Mr Moses' has 2 as a reference.

Is there any way I can combine these tables into one? Or is there perhaps an easier way of getting the data above into one row that I'm not seeing?

 

[Michael M]

Have you looked at Data / Text to Columns
and use the | as the delimiter

 

[themrg]

Hi Michael,

Hmmm.. Let me give it a go and I'll let you know if it worked.

 

[themrg]

Hi Michael,

Just thought I'd give you some feedback. I managed to get all my info I needed in one string of data using MS Word and the find and replace function.

First I removed the paragraph symbol that caused the text to separate (go find and replace ^13). Since every line started with the words "statement period" I could find and replace it with "^|statement period". This puts a manual spacing between each string. I then exported to .txt and imported it to excel using the| as a deliminator.

Thanks for your help!

 

[Michael M]

Well done, glad you got it sorted...
You obviously didn't need any help, you did it yourself....LOL!