Sorting

[luskentyre]

Hi all,

I am looking to do a mail shot from a publication but need some assistance in sorting the data from a pdf.

The data comes in the following format:

OK1234567 R The food company Ltd
Director(s) Mr Joe Blogs, Mrs Joe Blogs
Unt A, Some Road, Some town, Some county, Some post code
Operating centre: Another town, another county, another post code
Authorisation: 1 vehicle(s)

OK1234567 R The food company Ltd
Director(s) Mr Joe Blogs, Mrs Joe Blogs
Unt A, Some Road, Some town, Some county, Some post code
Operating centre: Another town, another county, another post code
Authorisation: 1 vehicle(s)

OK1234567 R The food company Ltd
Director(s) Mr Joe Blogs, Mrs Joe Blogs
Unt A, Some Road, Some town, Some county, Some post code
Operating centre: Another town, another county, another post code
Authorisation: 1 vehicle(s)

I have copied the above data into excel (2016) and used the text to columns format but this does not sort the data as I want it.

I have then copied each chunk of data and then used the paste special transpose function which gets me to more where i need to be but is there a quick way to get the data into the following format:

OK1234567 R, The Food Company Ltd, Director(s) Mr Joe Blogs, Mrs Joe Blogs, Unt A, Some Road, Some town, Some county, Some post code, Operating centre: Another town, another county, another post code

 

[Logit]

.
Do you want the resulting transposing to be in a single cell or can it be in different cells on the same row ?

In other words, do you want : "OK1234567 R, The Food Company Ltd, Director(s) Mr Joe Blogs, Mrs Joe Blogs, Unt A, Some Road, Some town, Some county, Some post code, Operating centre: Another town, another county, another post code"
to all be displayed in a single cell .... or can it be displayed in separate cells on the same row like this :




[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td][/td][td]OK1234567 R The food company Ltd[/td][td]Director(s) Mr Joe Blogs, Mrs Joe Blogs[/td][td]Unt A, Some Road, Some town, Some county, Some post code[/td][td]Operating centre: Another town, another county, another post code[/td][td]Authorisation: 1 vehicle(s)[/td][/tr]
[/table]

 

[luskentyre]

.
Do you want the resulting transposing to be in a single cell or can it be in different cells on the same row ?

In other words, do you want : "OK1234567 R, The Food Company Ltd, Director(s) Mr Joe Blogs, Mrs Joe Blogs, Unt A, Some Road, Some town, Some county, Some post code, Operating centre: Another town, another county, another post code"
to all be displayed in a single cell .... or can it be displayed in separate cells on the same row like this :




[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD][/TD]
[TD]OK1234567 R The food company Ltd[/TD]
[TD]Director(s) Mr Joe Blogs, Mrs Joe Blogs[/TD]
[TD]Unt A, Some Road, Some town, Some county, Some post code[/TD]
[TD]Operating centre: Another town, another county, another post code[/TD]
[TD]Authorisation: 1 vehicle(s)[/TD]
[/TR]
</tbody>[/TABLE]

It would be handy to get the data to be used as mail merge and therefore take the format:

OK1234567, R, The food company Ltd, Director(s), Mr Joe Blogs, Mrs Joe Blogs, Unt A, Some Road, Some town, Some county, Some post code etc etc etc

Is this possible?