Index/Collect into different columns depending on contract type

Samantha McDonald

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?

Samantha McDonald

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

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.

Paul Newcome

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

Samantha McDonald

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

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

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

Samantha McDonald

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

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

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

Paul Newcome

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

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

Paul Newcome

Happy to help. 👍️