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.

CA-Santosh's avatar
CA-Santosh
Copper Contributor
Apr 15, 2020

JonasRKorsholm 

 

Hi,

 

You can try the below formula

=CONCAT("Employees:",SUBSTITUTE(TEXTJOIN("-",TRUE,FILTER($A$2:$B$100,$A$2:$A$100=A2)),A2,"-"))

 

Attached file contains the above formula

 

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

    CA-SantoshInteresting

     

    If I add more rows or changes the employee names the function returns #NAME?, how come that?

     

    I have tried manually entering the function, although it returns the same result

    The function is to be used in a sheet with +500 rows. Where the table contains approx. 80 customers and 50 employees, hence the text string must contain a list of the employee names, as your suggestion does. Although it has to be applied to other customer names and employee names. 

    • wumolad's avatar
      wumolad
      Iron Contributor
      Apr 15, 2020

      JonasRKorsholm Are you using the latest version of Excel as the filter function is for those using Excel for Office 365, Excel for Office 365 for Mac, Excel for the web, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel for Android phones. The filter function embedded in the formula will not work if you have an older version.

      • JonasRKorsholm's avatar
        JonasRKorsholm
        Copper Contributor
        Apr 15, 2020
        I am using Excel 2016. Although I am unsure about the latest updated version. How may i check that?

Resources