I'm trying to concatenate different cell values into one single cell in a
dynamic table. Usually one could use =concatenate(), or do something like
=A1&" ,"&A2 and so on. What I want to try to do is concatenate an array of
values into a single cell. For example, =concatenate(B1:B5), but in this case
the result is an array equivalent to {=B1:B5}, which I find most disturbing.
Is there any way to do this I need to do?

If you don't mind VBA, then:

Function multicat(r As Range) As String
multicat = ""
For Each rr In r
multicat = multicat & rr.Value
Next
End Function
--
Gary's Student


"Vargasjc" wrote:

I'm trying to concatenate different cell values into one single cell in a
dynamic table. Usually one could use =concatenate(), or do something like
=A1&" ,"&A2 and so on. What I want to try to do is concatenate an array of
values into a single cell. For example, =concatenate(B1:B5), but in this case
the result is an array equivalent to {=B1:B5}, which I find most disturbing.
Is there any way to do this I need to do?

At this point I guess I'm gonna bear it. Although I'd like to make that a
user defined fucntion, UDF. How the heck do I do that?

"Gary''s Student" wrote:

If you don't mind VBA, then:

Function multicat(r As Range) As String
multicat = ""
For Each rr In r
multicat = multicat & rr.Value
Next
End Function
--
Gary's Student


"Vargasjc" wrote:

I'm trying to concatenate different cell values into one single cell in a
dynamic table. Usually one could use =concatenate(), or do something like
=A1&" ,"&A2 and so on. What I want to try to do is concatenate an array of
values into a single cell. For example, =concatenate(B1:B5), but in this case
the result is an array equivalent to {=B1:B5}, which I find most disturbing.
Is there any way to do this I need to do?

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".


http://www.mvps.org/dmcritchie/excel/getstarted.htm


It is my opinion that anyone clever enough to use Excel can work with VBA.

No one has proved me wrong.
--
Gary''s Student


"Vargasjc" wrote:

At this point I guess I'm gonna bear it. Although I'd like to make that a
user defined fucntion, UDF. How the heck do I do that?

"Gary''s Student" wrote:

If you don't mind VBA, then:

Function multicat(r As Range) As String
multicat = ""
For Each rr In r
multicat = multicat & rr.Value
Next
End Function
--
Gary's Student


"Vargasjc" wrote:

I'm trying to concatenate different cell values into one single cell in a
dynamic table. Usually one could use =concatenate(), or do something like
=A1&" ,"&A2 and so on. What I want to try to do is concatenate an array of
values into a single cell. For example, =concatenate(B1:B5), but in this case
the result is an array equivalent to {=B1:B5}, which I find most disturbing.
Is there any way to do this I need to do?

That's an interesting theory. And likely correct.
--
Brevity is the soul of wit.


"Gary''s Student" wrote:

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".


http://www.mvps.org/dmcritchie/excel/getstarted.htm


It is my opinion that anyone clever enough to use Excel can work with VBA.

No one has proved me wrong.
--
Gary''s Student


"Vargasjc" wrote:

At this point I guess I'm gonna bear it. Although I'd like to make that a
user defined fucntion, UDF. How the heck do I do that?

"Gary''s Student" wrote:

If you don't mind VBA, then:

Function multicat(r As Range) As String
multicat = ""
For Each rr In r
multicat = multicat & rr.Value
Next
End Function
--
Gary's Student


"Vargasjc" wrote:

I'm trying to concatenate different cell values into one single cell in a
dynamic table. Usually one could use =concatenate(), or do something like
=A1&" ,"&A2 and so on. What I want to try to do is concatenate an array of
values into a single cell. For example, =concatenate(B1:B5), but in this case
the result is an array equivalent to {=B1:B5}, which I find most disturbing.
Is there any way to do this I need to do?

Ok, I got the function working. Now I do something like =concat(B1:B10) and
it puts it all together. That's great. Thx.

Now I need also to do that with conditions, for example

=concat(IF(Alfa!E1137=Tables,Locations,0))

In which I'm conditionating the concatenation depending on a Cell value.
This works niceley as long as both Alfa and Locations have the same number of
rows. This is so because of the way seems to handle arrays. I haven't found a
solution to handle arrays by reference. Particularly using ifs. For example,
my problems would end if I could do something like:

=concat({IF(Alfa!E1137=Tables,Locations,0)})

Any help?

"Dave F" wrote:

That's an interesting theory. And likely correct.
--
Brevity is the soul of wit.


"Gary''s Student" wrote:

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".


http://www.mvps.org/dmcritchie/excel/getstarted.htm


It is my opinion that anyone clever enough to use Excel can work with VBA.

No one has proved me wrong.
--
Gary''s Student


"Vargasjc" wrote:

At this point I guess I'm gonna bear it. Although I'd like to make that a
user defined fucntion, UDF. How the heck do I do that?

"Gary''s Student" wrote:

If you don't mind VBA, then:

Function multicat(r As Range) As String
multicat = ""
For Each rr In r
multicat = multicat & rr.Value
Next
End Function
--
Gary's Student


"Vargasjc" wrote:

I'm trying to concatenate different cell values into one single cell in a
dynamic table. Usually one could use =concatenate(), or do something like
=A1&" ,"&A2 and so on. What I want to try to do is concatenate an array of
values into a single cell. For example, =concatenate(B1:B5), but in this case
the result is an array equivalent to {=B1:B5}, which I find most disturbing.
Is there any way to do this I need to do?