Concatenating Multiple Rows

[bmcleer]

So I am able to concatenate the same rows. I am trying to make a street index. Right now I have The same street name in multiple rows, but I would like One street name showing the Index with Commas. Example for right now: ROAD A: A1
ROAD A: A2, ROAD A: A3. So those three entries are each a row, and I would like it to read ROAD A: A1, A2, A3.
Thank you.

[tigeravatar]

bmcleer,

Unfortunately, Excel's native CONCATENATE() function doesn't work with arrays. This means that there is no way Excel can perform what you're asking for without the use of VBA or an add-in.
It is for this reason that I recommend the ConcatAll UDF I created:

Please Login or Register  to view this content.

How to use a User Defined Function (UDF):

1. Make a copy of the workbook the macro will be run on
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor

Then, you can use this formula to concatenate the items:

Please Login or Register  to view this content.