1

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

Name Contract Type
Employee1 Permanent
Employee2 Contract
Employee3 Intern
Employee4 Permanent
Employee5 Permanent
Employee6 Inter
Employee7 Contract

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.

Type Count Names
Permanent 4 Employee1,Employee4,Employee5,Employee14
Contract 3 Employee2,Employee7,Employee21
Intern 2 Employee3,Employee6

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

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
Joseph McCarthy
  • 897
  • 2
  • 19
  • 41

3 Answers3

3

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.

Mark S.
  • 1,474
  • 1
  • 6
  • 20
1

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.

Tom.Hendry
  • 31
  • 7
1

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.

Apostolos55
  • 574
  • 3
  • 8