Concatenate by checking a condition
 

Hi,

I am trying to concatenate cells after checking the condition. For example

A B
1 0
1 A
1 B
2 0
2 1
2 B

I would like to check the value of A and concatenate B. For A = 1, I am
looking for 0 A B. For A = 2, I am looking for 0 1 B.

Is there a function to do it automatic?

Thanks

Concatenate by checking a condition
 

Nothing built in to do this. There is a free add-in that has a function that
will do this:

http://www.download.com/Morefunc/300...-10423159.html

Then you can use an array formula** like this:

=TRIM(MCONCAT(IF(A1:A6=1,B1:B6&" ","")))

Note that the resulting string can't be longer than 255 characters.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Senthil" wrote in message
...
Hi,

I am trying to concatenate cells after checking the condition. For example

A B
1 0
1 A
1 B
2 0
2 1
2 B

I would like to check the value of A and concatenate B. For A = 1, I am
looking for 0 A B. For A = 2, I am looking for 0 1 B.

Is there a function to do it automatic?

Thanks

Concatenate by checking a condition
 

Valko, Thanks for your help. I installed the add-in function that you have
suggested, I am getting the error message "#NAME?". I couldn't quite
understand what to do with array formula? Can you please help.
Thanks.

"T. Valko" wrote:

Nothing built in to do this. There is a free add-in that has a function that
will do this:

http://www.download.com/Morefunc/300...-10423159.html

Then you can use an array formula** like this:

=TRIM(MCONCAT(IF(A1:A6=1,B1:B6&" ","")))

Note that the resulting string can't be longer than 255 characters.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Senthil" wrote in message
...
Hi,

I am trying to concatenate cells after checking the condition. For example

A B
1 0
1 A
1 B
2 0
2 1
2 B

I would like to check the value of A and concatenate B. For A = 1, I am
looking for 0 A B. For A = 2, I am looking for 0 1 B.

Is there a function to do it automatic?

Thanks

Concatenate by checking a condition
 

Valko, I had to restart my m/c after the download installation. It works
pretty good.

Thank you.

"T. Valko" wrote:

Nothing built in to do this. There is a free add-in that has a function that
will do this:

http://www.download.com/Morefunc/300...-10423159.html

Then you can use an array formula** like this:

=TRIM(MCONCAT(IF(A1:A6=1,B1:B6&" ","")))

Note that the resulting string can't be longer than 255 characters.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Senthil" wrote in message
...
Hi,

I am trying to concatenate cells after checking the condition. For example

A B
1 0
1 A
1 B
2 0
2 1
2 B

I would like to check the value of A and concatenate B. For A = 1, I am
looking for 0 A B. For A = 2, I am looking for 0 1 B.

Is there a function to do it automatic?

Thanks

Concatenate by checking a condition
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Senthil" wrote in message
...
Valko, I had to restart my m/c after the download installation. It works
pretty good.

Thank you.

"T. Valko" wrote:

Nothing built in to do this. There is a free add-in that has a function
that
will do this:

http://www.download.com/Morefunc/300...-10423159.html

Then you can use an array formula** like this:

=TRIM(MCONCAT(IF(A1:A6=1,B1:B6&" ","")))

Note that the resulting string can't be longer than 255 characters.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Senthil" wrote in message
...
Hi,

I am trying to concatenate cells after checking the condition. For
example

A B
1 0
1 A
1 B
2 0
2 1
2 B

I would like to check the value of A and concatenate B. For A = 1, I am
looking for 0 A B. For A = 2, I am looking for 0 1 B.

Is there a function to do it automatic?

Thanks