Index/Collect into different columns depending on contract type

Options

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
    Samantha McDonald ✭✭
    edited 10/10/23 Answer ✓
    Options

    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 ✓
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try switching your two cross sheet references around so that {Contract} is first.

  • Samantha McDonald
    Samantha McDonald ✭✭
    edited 10/10/23 Answer ✓
    Options

    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! 😎

  • Samantha McDonald
    Options

    Hey @Paul Newcome one final question! How would I do a count of Contract results? So if I had a cell which returned every contact type and I wanted to do a count on 3rd Party, SSA, etc. I can't get any of the below to work properly and it's the last piece of my puzzle 🤧

    =COUNTIF([CONTRACT TYPE]@row, CONTAINS(@cell, "3rd Party"))

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

    =COUNTIF([CONTRACT TYPE]:[CONTRACT TYPE], FIND("LMSA", @cell) > 0)


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    What would be the expected output for "3rd Party"?

  • Samantha McDonald
    Options

    It should be a number but I've looked at my roadmap again and I can remove this step if I just do a count of names returned instead 🤦‍♀️

    Hopefully my explanation of what I'm trying to achieve below is helpful!

    We currently have a dropdown, multi-select column for employees assigned to a particular job. I then have a column which does a count of the number of employees selected: =COUNTM([EMPLOYEE NAME]@row)

    I have another column which tells me which employees are on a full time contract (LMSA): =JOIN(COLLECT({Name}, {Contract}, "LMSA", {Name}, CONTAINS(@cell, [EMPLOYEE NAME]@row)), ", ")

    I then need a count of the names in this column. I was hoping this formula would work again but it only brings back an answer of one each time: =COUNTM([EMPLOYEE NAME]@row)

    End goal is that we know we have six employees assigned to X job and of these, four are on a full time (LMSA) contract and therefore no action is required. However two are 3rd Party so a contract will need to be drawn up and signed for Mike and Steve before we can start the job.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I understand that it should be a number, but what would be the number expected ONLY based on your most recent screenshot? Would it be 2 because there are 2 cells with "3rd Party" in them, or would it be 9 because there are a total of 9 occurrences of "3rd Party"?

  • Samantha McDonald
    Options

    Oh sorry! It would be per row so 5 and 4

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

    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.

  • Samantha McDonald
    Options

    Oh you genius!!! Thanks so much for your help Paul!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!