Repeating a concatenate function

[Guest]

Any ideas out there? I have a spreadsheet with an unknown number of rows
(1-100). I am trying to concatenate in one cell the output of each row. I
can use a count function to tell me how many rows to concantenate, but is
there some type of repeat function that will move down one row each time?
ie. if i have the following names in Column A, I want this result in Column C.

"A" "B" "C"
Carl Carl, Nick, Fred, Sam
Nick
Fred
Sam

Thanks in advanced

 

[Mike]

I would just write a macro to loop round the appropriate number of
times - looping is something Excel isn't good at doing but if you
combine the power of simple macros and the functionality of Excel,
there's not much you can't achieve. If you haven't used macros before,
simply record one and edit the results. The syntax for addressing
particular cells and creating loops is pretty straightforward.

Mike

 

[Mike]

I would just write a macro to loop round the appropriate number of
times - looping is something Excel isn't good at doing but if you
combine the power of simple macros and the functionality of Excel,
there's not much you can't achieve. If you haven't used macros before,
simply record one and edit the results. The syntax for addressing
particular cells and creating loops is pretty straightforward.

Mike

 

[Guest]

You can create a UDF to perform this. To do this follow these steps:

1) Alt-F11
2) In the left pane right click on your sheet name and choose <Insert><Module>
3) Paste the following code:

Function concat(rng As Range)
For Each rng In rng
temp = temp & rng & ", "
Next
concat = Left(temp, Len(temp) - 2)
End Function

4) Then you can use the function concat() within your sheet (e.g.
=concat(A1:A10))

 

[Gord Dibben]

David

A small problem with your UDF..............

If any cells are blank in the A1:A10 range you get extra commas.

I.e. a,b,c,,,g,h,i,j

This modified UDF does not have that problem.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function


Gord Dibben MS Excel MVP

 

[Harlan Grove]

Gord Dibben wrote...
....

This modified UDF does not have that problem.

....

But it's not general. Why not make the comma delimiter another
argument? But then again, why restrict the udf unnecessarily to ranges?
Also, what happens if the OP *wants* to include blank fields between
comma separators? Choice is good.

The mcat udf in

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/94456a9e326b19a6

(or http://makeashorterlink.com/?C6B84239D ) would allow for variable
number of arguments, and could be used for this specific task in an
array formula like

=SUBSTITUTE(TRIM(mcat(A1:A8&" "))," ",", ")

or just

=SUBSTITUTE(mcat(A1:A8&" ")," ",", ")

if blank fields should be included.