Concatenating with thousands of rows

[thedimd]

Hi,

I apologize for the vague nature of the question, but I truly can't figure out a better way to word this. Here is what I need to do:

I have thousands of rows similarly structured as below. They need to be concatenated like this:

Joe Blow Daffy Duck Bugs Bunny Elmer Fudd 1122
Daffy Duck Bugs Bunny Joe Blow Daisy Duck 6452

With an end result of:

Joe Blow 1122 Daffy Duck 1122 Bugs Bunny 1122 Elmer Fudd 1122 1122
Daffy Duck 6452 Bugs Bunny 6452 Joe Blow 6452 Daisy Duck 6452 6452

Please note that every name and number combination must remain it's own cell... which is the reason for my question. Does that make sense? Anyone have any ideas?


Thanks!

Ryan

[ChemistB]

It would be easier to see what you are talking about if you posted it as an Excel Sheet (Go Advanced>Manage Attachments)
Show your example and what you would like to see as a result.

[thedimd]

Apologies!

Here we go...Book1.xlsx

I know there has to be a way to do this with multiple cells in the same row, but am just hitting a wall.


Thanks for any help!

[ChemistB]

Easiest way is with VBA (alternate way involves formulas in other columns and pasting back over original. Let me know if you prefer that)

Open a module

ALT + F11
Insert > Module

and paste in this code (ranges are set for your example so try on that first)

Please Login or Register  to view this content.

Reset the ranges for your real sheet if it is working for you. Don't run it twice or you'll have the value from Col G in twice!

[thedimd]

Reputation added. Thank thank you so much for the assist!