Generating af list in a cell

[JonasRKorsholm]

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 list of the employees related to the customer in the text column using a function. I have tried =GETPIVOTDATA, but it does not seem to do the trick in a single cell.

Hence it should look like this:

Customer	Employee	Text
Customer 1	Jan	Employees: Jan, Jerry, Kara
Customer 1	Jerry	Employees: Jan, Jerry, Kara
Customer 2	Liza	Employees: Liza
Customer 1	Kara	Employees: Jan, Jerry, Kara

Looking forward to hearing your ideas if possible

[#2]

@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

[#3]

@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. 

[#4]

@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.

[#5]

I am using Excel 2016. Although I am unsure about the latest updated version. How may i check that?

[#6]

@JonasRKorsholm if you are using Excel 2016, the filter function is not available. 

[#7]

@wumoladOK, it works if I open it online. Thank you!

In the real datatable, there will be reoccurances, because the master data is over several days, hence reoccurances will occur. @CA-Santosh Is it possible to make a distinct summary in the list?

I have included an example below, where Jan occurs twice. 

Customer	Employee	Text
Customer 1	Jan	Employees:--Jan---Jerry---Kara---Jan
Customer 1	Jerry	Employees:--Jan---Jerry---Kara---Jan
Customer 2	Liza	Employees:--Liza
Customer 1	Kara	Employees:--Jan---Jerry---Kara---Jan
Customer 3	Carl	Employees:--Carl
Customer 1	Jan	Employees:--Jan---Jerry---Kara---Jan

[#8]

@JonasRKorsholm To use Excel 2016, you can use this formula =CONCAT("Employees: ",TEXTJOIN(", ",TRUE,IF($A$2:$A$15=A6,$B$2:$B$15,""))) and then make it an array formula by pressing "Ctrl + Shift + Enter"

I will look at the reoccurrence and revert.

[#9]

@JonasRKorsholm , @wumolad ,

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

[#10]

@Sergei Baklan Well done. I was about posting similar formula to resolve the duplicate item

[#11]

@Sergei BaklanThank 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;
            "");
        ""));
"")

[#12 · Accepted solution]

@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.