## [Solved] Get multiple strings comma separated into one cell

### [Solved] Get multiple strings comma separated into one cell

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
Last edited by Hagar Delest on Fri Oct 19, 2018 8:33 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.5 on Windows 7 64-bit
elsiwantstoknow

Posts: 2
Joined: Wed Oct 17, 2018 3:20 pm

### Re: Get multiple strings comma separated into one cell

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   Expand viewCollapse view
`{=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.
Attachments
aoo95545_AccumulateTextsIf_1.ods
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2369
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: Get multiple strings comma separated into one cell

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 26542
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Get multiple strings comma separated into one cell

Lupp wrote: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   Expand viewCollapse view
`{=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.
OpenOffice 4.1.5 on Windows 7 64-bit
elsiwantstoknow

Posts: 2
Joined: Wed Oct 17, 2018 3:20 pm