[Solved] Get multiple strings comma separated into one cell

Discuss the spreadsheet application

[Solved] Get multiple strings comma separated into one cell

Postby elsiwantstoknow » Wed Oct 17, 2018 6:02 pm

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

Postby Lupp » Wed Oct 17, 2018 7:12 pm

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
(19.02 KiB) Downloaded 6 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2101
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Get multiple strings comma separated into one cell

Postby Villeroy » Wed Oct 17, 2018 7:56 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 25862
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Get multiple strings comma separated into one cell

Postby elsiwantstoknow » Thu Oct 18, 2018 8:37 am

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


Return to Calc

Who is online

Users browsing this forum: Invictus, RPG and 51 guests