Forum Discussion

JonasRKorsholm's avatar
JonasRKorsholm
Copper Contributor
Apr 15, 2020
Solved

Generating af list in a cell

Hi   I have a table in excel similar to   Customer Employee Text Customer 1 Jan   Customer 1 Jerry   Customer 2 Liza   Customer 1 Kara     Is it possible to create a...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 17, 2020

    JonasRKorsholm 

    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.

JonasRKorsholm's avatar
JonasRKorsholm
Copper Contributor
Apr 17, 2020

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;
            "");
        ""));
"")

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 17, 2020

JonasRKorsholm 

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.

Resources