textjoin issues

[saravana0306]

Hello Everyone , 

I have the text join formula to combine a series of text in a row. 

But the cells have a vlookup formula to get data from another sheet. When try to join the text resulted in the cells by the vlookup formula, the formula of textjoin does not work.

Please see the image i have added a better understanding .

Text Join :  =TEXTJOIN(" | ";TRUE;IF(COLUMN(F4:Y4)=MATCH(F4:Y4;F4:Y4;0);F4:Y4;""))

Formula in the cells :=IFNA(VLOOKUP($A4&COLUMNS(F3:$F4);'Reception '!$M$2:$N$50000;2;0);"")

Help please 

[#2]

@saravana0306 

My guess would be:

=TEXTJOIN(" | ";TRUE;IF(COLUMN(F4:Y4)-COLUMN($F4)+1=MATCH(F4:Y4;F4:Y4;0);F4:Y4;""))

[#3]

@Detlef Lewin 

Thanks much for the time . Unfortunately, this one does not work as well. 

Do you think it is because the there is some formula already there in the cell ?

[#4]

@saravana0306 

It worked for me.

Could you upload your file?

[#5]

I'd wrap by IFERROR

=TEXTJOIN(" | ",TRUE,IFERROR(IF(COLUMN(F4:Y4)-COLUMN($F4)+1=MATCH(F4:Y4,F4:Y4,0),F4:Y4,""),""))

[#6]

@Detlef Lewin  Sure . Here it is. 

[#7]

@saravana0306 

Please be kind to the helpers and do not upload such big files that take ages to load and to calculate.

I deleted some empty rows and some formulas and the conditional formatting because they were all slowing down the spreadsheet. That left 1.7 MB from the original 9 MB.

I created a different solution using Power Query instead of Excel's TEXTJOIN().

The result is still shown as a pivot table.

[#8]

@Detlef Lewin , My sincere apologies for the inconvenience caused. This won't you repeated again.

Thank you very much for the help and time.

[#9]

@saravana0306 

I investigated the issues you mailed to me.

The problem is there are additional blanks after some texts. To deal with that I inserted a Trimmed Text-step in tbl_Reception.

To unify the order of the text blocks I inserted List.Sort into the Extracted Values-step in qry_Reception.

= Table.TransformColumns(#"Added Custom", {"Temp", each Text.Combine(List.Transform(List.Sort(_), Text.From), "|"), type text})

I hope it helps.

[#10]

@Detlef Lewin , It worked fantastically. 

Thank you very much for the help.

Br.,

Saravana

[#11]

@saravana0306 

You're welcome.