concatenate.if

[van gysel]

I would like to have a formula like
{=concatenate(if(B1:b10=1;a1:a10)}
to concatenate the elements of a1 to a 10 if the cells B1 to B10 are equal to 1

 

[BBD]

Hi Van,

I have not come accross a built-in to concatenate a range nor any combination of functions to develop that.

But a small vba code will help you create a custom formula to do that. Here is the code.

Public Function Cuncat(rng As Range)
Dim cell As Range
Application.Volatile
For Each cell In rng
  Cuncat = cell & Cuncat
Next cell
End Function

And a sample file below

Cheers,
BD

 

[ThrottleWorks]

Hi Van,

Will this work ?

=IF(COUNTIF(B1:B10,1)=10,CONCATENATE(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,""))

 

[van gysel]

Not really
I will write a VBA macro then , a pity that Excel did not make a formula that accepts ranges
thanks anyways

 

[ThrottleWorks]

Hi Van,

Thanks for the feedback.

I think I did not understand your query properly, is it possible to upload a sample file.

If I am not wrong, all the formulas work on the basis of Ranges only.

 

[van gysel]

I found a solution but ugly see attached file

 

[Hui]

Have a read of: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Including the comments
There is some good stuff there that will help you

 

[van gysel]

Thanks I read it also but I think I will use help columns since I master the application (internal)
in fact it is because I want the operator to choose the sheets of the book that must be saved in pdf and the ones that must be saved in xls
the macro use the concatenation to build an array of the sheets to save in the appropriate way