Forum Discussion
Generating af list in a cell
Formula doesn't show "Employees: " if any of the Customer or Employee values are empty. Perhaps more correct will be
=IF((A2<>"")+(B2<>""), "Employees: " & TEXTJOIN(", ",TRUE, IF($A$2:$A$15=A2, IF( IFNA(MATCH($B$2:$B$15,$B$2:$B$15,0),"") = (ROW($B$2:$B$15)-ROW($B$1)), $B$2:$B$15, ""), "")), "")where we at first check if at least at least one of the field has data, plus wrap MATCH with IFNA. Please check in attached.
It could be modified as
=IFNA("Employees: " &
TEXTJOIN(", ",TRUE,
IF($A$2:$A$15=A2,
IF(MATCH($B$2:$B$15,$B$2:$B$15,0) = (ROW($B$2:$B$15)-ROW($B$1)),
$B$2:$B$15,
""),
"")),
"")Result is
SergeiBaklanThank you very much. The function is mostly working. Although in some instances, the text cell is left empty with "Employee: " or missing some employees in the list.
I have tried to replicate the issue in the spreadsheet that you've attached, although it wasn't possible.
I have copied the function to my spreadsheet, adjusted the columns and fixed the area into a larger scale and lastly formatted the cell as an array using ctrl+shift+enter. See function below
What may cause the issue?
=IFNA("Employees: " &
TEXTJOIN(", ";TRUE;
IF($D$2:$D$9999=D4;
IF(MATCH($E$2:$E$9999;$E$2:$E$9999;0) = (ROW($E$2:$E$9999)-ROW($D$1));
$E$2:$E$9999;
"");
""));
"")
Formula doesn't show "Employees: " if any of the Customer or Employee values are empty. Perhaps more correct will be
=IF((A2<>"")+(B2<>""), "Employees: " & TEXTJOIN(", ",TRUE, IF($A$2:$A$15=A2, IF( IFNA(MATCH($B$2:$B$15,$B$2:$B$15,0),"") = (ROW($B$2:$B$15)-ROW($B$1)), $B$2:$B$15, ""), "")), "")where we at first check if at least at least one of the field has data, plus wrap MATCH with IFNA. Please check in attached.