[Solved] Get multiple strings comma separated into one cell

[elsiwantstoknow]

Hi,

I created a table to calculate design project costs. Everything works perfectly, but I want to add one little feature.

I use this formula to sum up special costs by choosing various options with radio buttons (F44:F75). In C44:C75 I calcute the costs for every single option.
=SUMIF(F44:F75;"1";C44:C75)

Now I need something similar, what instead of summing up the costs of the choosen options, shows the title of these option comma separeted in ONE cell. These titles are written in B44:B75.
In short: look up F44:F75 for TRUE, get the name of the true options from B44:B75 and put them comma seperated into ONE cell.

I already used different approaches (IF(), ISNA(), MATCH(), B44 & ", " & B45,...), but everything resulted into errors or only a single option was printed in to the cell. It seems not to be as easy as calculating.

Can anybody help me with this?

Thanks!
Elsi

[Lupp]

According with the specifying document (OASIS) OpenFormula V1.2 there is no accumulating function for strings in Calc.

Since Excel 2016 and/or "Excel 360" introduced a function for the purpose, the LibreOffice development drew the general clause concerning the implementation of additional functions and implemented the TEXTJOIN() function which is capable of doing what you want. It is usable as intended in versions 5.3.4 and higher there. The obvious completion by a TEXTSPLIT() function is still missing in LibO and in Excel as well.
A formula that should do what you want is

Code: Select all

{=TEXTJOIN(",";1;IF(F44:F75="1";B44:B75;""))}

entered for array-evaluation.
BTW: Why do you treat the criterion as Text "1" (most likely as opposed to "0") instead of number 1 or logical?
If you consider to try a recent LibO (a portable package probably) please also note that accumulated texts can easily get very long.

A solution using lots of helper cells is also demonstrated in the attached document.

[Villeroy]

You want a GROUP_CONCAT which is an aggregate function in database applications. Spreadsheets can't do that unless you program something to do the trick.

Some database applications can create from this table ...

A | Seatle | <more data in other columns>
B | Rome
A | Paris
C | Beijing
B | Dakar

... this table:
A | Seatle, Paris
B | Rome, Dakar
C | Beijing

See [Python,Calc] Group_Concat for the spreadsheet

[elsiwantstoknow]

According with the specifying document (OASIS) OpenFormula V1.2 there is no accumulating function for strings in Calc.

Since Excel 2016 and/or "Excel 360" introduced a function for the purpose, the LibreOffice development drew the general clause concerning the implementation of additional functions and implemented the TEXTJOIN() function which is capable of doing what you want. It is usable as intended in versions 5.3.4 and higher there. The obvious completion by a TEXTSPLIT() function is still missing in LibO and in Excel as well.
A formula that should do what you want is

Code: Select all

{=TEXTJOIN(",";1;IF(F44:F75="1";B44:B75;""))}

entered for array-evaluation.
BTW: Why do you treat the criterion as Text "1" (most likely as opposed to "0") instead of number 1 or logical?
If you consider to try a recent LibO (a portable package probably) please also note that accumulated texts can easily get very long.

A solution using lots of helper cells is also demonstrated in the attached document.

Thanks! The helper-cell-way works perfectly for me and does exactly what I want.