Combine multiple rows of data into one long string

[pocquet]

Hi guys,

I have a data set that is by its nature has multiple lines for a single reference number.

So for example:

[FONT=Calibri][COLOR=#000000][/COLOR][/FONT][TABLE]
<caption>[B]Qry_Master_Vendor_Output_L1[/B]</caption><thead>[TR]
[TH="bgcolor: #c0c0c0"][FONT=Calibri][COLOR=#000000]CPVDescription[/COLOR][/FONT][/TH]
[TH="bgcolor: #c0c0c0"][FONT=Calibri][COLOR=#000000]Vendor[/COLOR][/FONT][/TH]
[/TR]
</thead><tbody>[TR]
[TD][FONT=Calibri][COLOR=#000000]Sludge disposal services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Sludge treatment services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Sludge transport services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Sludge removal services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Treatment and disposal of foul liquids.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Refuse incineration services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Ash disposal services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Household-refuse disposal services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Urban solid-refuse disposal services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
</tbody><tfoot></tfoot>[/TABLE]

Is it possible to combine all results in CPV descirption so they appear as a single row eg:

[TABLE="width: 328"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]CPVDescription[/TD]
[TD]Vendor[/TD]
[/TR]
[TR]
[TD]Sludge disposal services.Sludge treatment services.Sludge transport services.Sludge removal services.Treatment and disposal of foul liquids.Refuse incineration services.Ash disposal services.Household-refuse disposal services.Urban solid-refuse disposal services.[/TD]
[TD]1000049892[/TD]
[/TR]
</tbody>[/TABLE]

But with spaces rather than the period?

Thanks

 

[JeanKim]

you want formula? or macro?

 

[JeanKim]

if you want formula:
Use substitute function as below example

=SUBSTITUTE(A1&A2&A3&A4&A5&A6&A7&A8&A9,"."," ")

 

[pocquet]

Hi mate,

Thanks for the reply.

I need a solution within Access ideally as I need to do this for 100k rows of data and excel just dies on its *** trying to do this

 

[JeanKim]

1. This is excel forum
2. I work with over 200k rows everyday so I believe there is solution with excel macro.
3. I only replied to what you asked. If you explain clearly what you need then I am sure someone will help you.

 

[pocquet]

Hi,

A macro would be great either or. I just don't think its practical do it with formulas given I have tried multiple ways and I either crash out or they don't work.

As an FYI, I understand that this is primarily an excel fourm (Eg mrexcel.com) however it does contain a subsection for Access related questions as well as power BI etc. This can be evidenced here http://www.mrexcel.com/forum/forum.php which shows Question fourms of which access is posted.

This question is posted in the correct part of the forum thanks

 

[JeanKim]

Hi,

A macro would be great either or. I just don't think its practical do it with formulas given I have tried multiple ways and I either crash out or they don't work.

I agree that formula is impractical which was not evident in your original example. As I said before I run macro for 200K lines everyday without any problem. If you already have code that crash or something, post them here so we have better idea of your intent and situation.

As an FYI, I understand that this is primarily an excel fourm (Eg mrexcel.com) however it does contain a subsection for Access related questions as well as power BI etc. This can be evidenced here http://www.mrexcel.com/forum/forum.php which shows Question fourms of which access is posted.

This question is posted in the correct part of the forum thanks

Although I disagree, I am not going to argue since I am a newcomer myself.

 

[Micron]

1. This is excel forum

This was correctly posted in the Access part of the forum; someone disagreeing with you won't change that.
For an Access solution, a bit more info is required. If that is table data you have provided, it is correctly structured and putting it into one long string is an unusual goal. What would you do with this long concatenated string? Store it in a table (not good)? Where do you want to put/use it?
Edit: seems to me that using spaces instead of a separator character would result in a long string of one-off words whose only apparent separation from some other portion is a capital letter, which probably can't be guaranteed.

Sludge disposal services Sludge treatment services Sludge transport services.

 

[JeanKim]

Mybad... I didn't know I was in access forum

 

[Micron]

You are not the first. Not real obvious, other than the path displayed at the top of your screen.