Index/Collect into different columns depending on contract type

I have a multi select column with a list of employees. Each of these have a different contract type which is listed in a seperate sheet. I want to keep the master list of employees on my current sheet but then break it down into columns of whose contract is permanant, part-time or contract worker.

This gives me a list of contract types but I can't seem to deduce myself how to change it to return the employee names into the correct columns for contract type.

=INDEX(COLLECT({Name}, {Contract}, [EMPLOYEE NAME]@row), 1)

Any thoughts?

Best Answers

  • Samantha McDonald
    edited 10/10/23 Answer ✓

    I've used this formula but it returns all contract types for all employees on that row.

    =JOIN(COLLECT({Contract}, {Name}, CONTAINS(@cell, [EMPLOYEE NAME]@row)), " , ")

    I think I've sorted it by including the contract type to search for and then return the names of those who have that contracr type.

    =JOIN(COLLECT({Name}, {Contract}, "3rd Party", {Name}, CONTAINS(@cell, [EMPLOYEE NAME]@row)), " , ")

    I must have been having a Friday afternoon brain fart so thanks Paul! 😎

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    On a row by row basis, you would need separate columns for each selection type. So a column for the 3rd Party count, a column for the LMSA count, so on and so forth. From there you would use this to get your counts:

    =(LEN([Contract Type]@row + ",") - LEN(SUBSTITUTE([Contract Type]@row + ",", "abc", ""))) / LEN("abc")


    Each instance of "abc" in the above formula should be replaced with whatever you are wanting to get the count for.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!