I have spreadsheet of employees and their contract type, in the following form

and so on up to EmployeeN

I've to count the number of employees, and create a CSV list of employee names of each type e.g.

This needs to update automatically, so if Employee2's contract type is changed to Permanent, the count in the Contract row becomes 2, the count in the Permanent row becomes 5, and the list of names is updated

I've used COUNTIFS to count the number of each employee type, but can't update the list of names

I've tried to create a range of names using VLOOKUP but it's only returning a single value, and I need multiple values to use the CONCATENATE method

CodePudding user response：

If you're on Excel 365 you can use the function Textjoin combined with Filter

=Textjoin(",",TRUE, Filter([Name Column], [Contract Column] = [Contract Cell]))

This will combine the names and separate the names with a comma according to where the Contract column is the same as the contract cell reference. Replace the variables within [ ] with the cell references, ensuring the lock them properly.

If you use a table format by hitting CTRL T you can name the references so as the table expands or changes it will still refer to the proper ranges.

Using Unique you can also get a list of all the contract types in case those get additions.

CodePudding user response：

You are looking for =TEXTJOIN.

=TEXTJOIN(Delimiter,TRUE,IF(ConditionRange=Condition,Range,""))

In your case "Delimiter" would be ",". ConditionRange would be the range containing either "Permanent", "Contract" or "Intern" in your data. Condition would be the Type on the current row and the Range would be the range of cells containing the Employee names in the data.

CodePudding user response：

I propose a VBA based solution with an UserDefinedFnction, MyConCat(). If you are allowed to use VBA then look into this older post for MyConCat

The function is used like Google-Sheet's "join", ex: =myConCat("/",b3:b5) or: [=myConCat(",", if(employees!B2:B20=A2, employees!A2A20, "") )]

formula in [] is an array so edit then press "ctrl shft Enter" instead of simple enter...

Again, if company policy prevents VBA and Excel doesn't have TextJoin() then consider Google-Sheets.