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.
JonasRKorsholm if you are using Excel 2016, the filter function is not available.
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 |
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.
SergeiBaklan Well done. I was about posting similar formula to resolve the duplicate item
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.