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
-
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! 😎
-
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
-
Try switching your two cross sheet references around so that {Contract} is first.
-
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! 😎
-
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)
-
What would be the expected output for "3rd Party"?
-
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.
-
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"?
-
Oh sorry! It would be per row so 5 and 4
-
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.
-
Oh you genius!!! Thanks so much for your help Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!