Textjoin not working properly

Conniegonzalez · August 29, 2023, 12:33am

Hi there!

I recently moved from Excel to LibreOffice and I am working with files that I used to work with in excel with no problems. However I am facing this issue in Libre Office Calc and I would love if someone could give me a hint with this matter, this is the formula I am using:

=TEXTJOIN("/ ",0,IF($E$2:$E$281=E3,$N$2:$N$280,N2))

The purpose of $E$2:$E$281=E3 is to find all cells that are duplicated in column E and merge the content of those cells that is in column N.

In excel, this cell brought the data in N only for those cells in E that were True, in LibreOffice it is bringing all data in the range from N2 to N280. It is working find if it is false.

How do I make it filter only the ones that are actually duplicated?

Here it is an example:

E  N   Current Result          Expected Result 
A  1   1                       1
B  2   2                       2
C  3   1/2/3/4/5               3/4/5
C  4   1/2/3/4/5               3/4/5 
C  5   1/2/3/4/5               3/4/5

I´d appreciate your help.

[Slightly edited by @Lupp for better readability.]

mariosv · August 29, 2023, 1:36am

Please attach a sample file.

mikekaganski · August 29, 2023, 6:23am

Yes, without an example, it is hard to know what is expected. The formula given, besides dealing with different-sized ranges ($E$2:$E$281 vs. $N$2:$N$280), does not allow to see which is the respective rows in the example given (is the A 1 row 2? If so, then the “current result” for this data is not as shown…)

Conniegonzalez · August 29, 2023, 10:28am

Dear Mike,

Thanks for your kind reply. I tried to give an idea on the example by putting dara in column E and N as I have it. The formula is obviously not working this is the help I need.

Could you please tell me which formula should I use to get the expected result?

I’d appreciate your help.

Lupp · August 29, 2023, 10:43am

Please prefer the semicolon as the argument delimiter. It is still accepted by any locale.
IF($E$2:$E$281=E3;$N$2:$N$280;N2)
The two ranges here need (imo) to be equally dimensioned, but the first one has 280 rows while the second one only has 279. Didn’t you get an error message?

Lupp · August 29, 2023, 11:05am

Seems I missed the point. I thought the implementation of TEXTJOIN() in Calc was equivalent to its Excel sister since V 4.4.
Still don’t understand the given formula and the claimed Excel result.
For the “filtering” with the help of empty strings see my answer.

erAck · August 29, 2023, 3:43pm

The = operator takes scalar values as operands and IF() takes scalar values as condition. For this part to work (if the intention was to evaluate an array of condiions and return an array) the formula has to be entered as an array formula (Shift+Ctrl+Enter), otherwise one implicit intersection of the formula cell’s position with the range $E$2:$E$281 is created as reference to be compared with E3 and one with $N$2:$N$280 to return a value.

They would get #VALUE! if the formula was entered in a cell not on rows 2 to 280 due to no implicit intersection being there. They would get #N/A if it was entered as array formula anywhere due to the mismatching array sizes.

Isn’t it? Where is the difference? Does Excel automatically force array context onto its third argument? Or something else?

Lupp · August 29, 2023, 3:56pm

I still think it’s equivalent but I can’t test with Excel. I obviously expressed myself not unmistakably.

Lupp · August 29, 2023, 11:07am

=TEXTJOIN("/";1;IF($E$2:$E$281=E2;$N$2:$N$281;""))

should do.
See attached spreadsheet document.
ask95182SpecialTextJoin.ods (17.6 KB)
[Sorry. My post crossed the one by @PKG]

Conniegonzalez · August 31, 2023, 2:27pm

To Everyone, Thanks so much for your help the final formula to make work was this one:

{=TEXTJOIN(" ";1;IF($T$2:$T$281=T2;$N$2:$N$281))}

The main issue I was having was the Array option, I also changed the separator as suggested and at the beginning didn´t work with all of these but when I used copy and paste instead of dragging the formula down it did the magic.

I am not sure how the separator and this copy and paste would differ from the other options, but all of these combined solutions made it work which was the purpose. Thanks to all for your quick feedback.

JohnSUN · August 31, 2023, 2:43pm

Try to drag with hold Ctrl key

Lupp · August 31, 2023, 2:55pm

contains an incomplete IF() expression. In case of a missing else-part like in this case, IF() is specified to return FALSE if the condition comes out false.
On the other hand TEXTJOIN() in skip_empty mode (here chosen by the 1 as the second argument) is specified to skip empty strings given by the input. FALSE isn’t an empty string, but a logical (Boolean) value, and would be automatically converted to "0" here due to general rules.
In different words: The “undocumented feature” that in the special case the missing alternative is treated as if meaning the empty string should actually be seen as a bug - and may be bug-fixed one day. Therefore: Use the complete IF() expression containing "" in the place of the alternative, and don’t rely on an unclear and undocumented behaviour.
(I would even recommend to never use incomplete IF() expressions though they are allowed, unfortunately.)
Another unsolicited advice: Don’t use a space as the list separator if not clearly needed. Conflicts expected.

Conniegonzalez · August 31, 2023, 4:05pm

Thanks for your reply, this is working perfect as it is, the recommendations you are giving me is how it was originally in the excel but here it doesn´t work. Don´t ask me why, that was my whole confusion, I am good at excel, here at Libreoffice I am just a newbie. I will keep your recomendations in mind in case one day it stops working.

Conniegonzalez · August 31, 2023, 4:05pm

I will keep it in mind. Thanks!