textjoin issues

Question

Hello Everyone ,&nbsp;&nbsp;I have the text join formula to combine a series of text in a row.&nbsp;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.&nbsp;Please see the image i have added a better understanding .&nbsp;Text Join :&nbsp;&nbsp;=TEXTJOIN(" | ";TRUE;IF(COLUMN(F4:Y4)=MATCH(F4:Y4;F4:Y4;0);F4:Y4;""))Formula in the cells :=IFNA(VLOOKUP($A4&amp;COLUMNS(F3:$F4);'Reception '!$M$2:$N$50000;2;0);"")&nbsp;Help please&nbsp;

detlef_lewin · Answer

saravana0306&nbsp;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.&nbsp;I created a different solution using Power Query instead of Excel's TEXTJOIN().The result is still shown as a pivot table.&nbsp;

detlef_lewin · Answer

saravana0306&nbsp;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&nbsp;tbl_Reception.To unify the order of the text blocks I inserted List.Sort into the&nbsp;Extracted Values-step in&nbsp;qry_Reception.= Table.TransformColumns(#"Added Custom", {"Temp", each Text.Combine(List.Transform(List.Sort(_), Text.From), "|"), type text})&nbsp;I hope it helps.&nbsp;

detlef_lewin · Answer

saravana0306&nbsp;My guess would be:=TEXTJOIN(" | ";TRUE;IF(COLUMN(F4:Y4)-COLUMN($F4)+1=MATCH(F4:Y4;F4:Y4;0);F4:Y4;""))&nbsp;

saravana0306 · Answer

Detlef_Lewin&nbsp;&nbsp;Thanks much for the time . Unfortunately, this one does not work as well.&nbsp;&nbsp;Do you think it is because the there is some formula already there in the cell ?

detlef_lewin · Answer

saravana0306&nbsp;It worked for me.Could you upload your file?&nbsp;

Forum Discussion

textjoin issues

10 Replies

Resources