populate value within the same ID number with multiple criteria
Hi,
I'm trying to create a formula that will populate Column Outcome (example highlighted in red). Within the same ID#, it'll populate the assignee based on:
1.) Within the same ID #, if column 5 has a %, and the assignee is not Sam, it'll populate the assignee name within that ID #
2.) If it's Sam, then it'll look at column 4 and see who has the higher % within the same ID # and it'll populate that name
Also, is there a formula that can assign alternatively assign 0 or 1 based on the ID# (example highlighted in red)? I wanted to use a conditional format to highlight the row so it's easier to view for the same ID.
Thank you!
Best Answer
-
@Christina09 This formula is getting out of control 😁
Try this one, and let me know if it's any better:
=IF(IF(JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], >0, ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam"), "") = "", JOIN(COLLECT(Assignee:Assignee, [Column4]:[Column4], >0, ID:ID, ID@row, Select:Select, 1, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam")), Assignee:Assignee, "<>Sam"), ""), JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], >0, ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam"), "")) = "", "Chris", IF(JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], >0, ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam"), "") = "", JOIN(COLLECT(Assignee:Assignee, [Column4]:[Column4], >0, ID:ID, ID@row, Select:Select, 1, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam")), Assignee:Assignee, "<>Sam"), ""), JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], >0, ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam"), "")))
Answers
-
@Christina09 This is a tricky one! When would Sam be assigned? ever?
This is where I'm at so far:
=IF(COUNTIFS(Assignee:Assignee, "<>Sam", [Column5]:[Column5], "<>", ID:ID, ID@row) >= 1, JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], "<>", Assignee:Assignee, "<>Sam", ID:ID, ID@row), ""), JOIN(COLLECT(Assignee:Assignee, Assignee:Assignee, "<>Sam", ID:ID, ID@row, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], ID:ID, ID@row))), ""))
-
Thank you! It works. Ya, Sam wouldn't be assigned 😅
Do you happen to know if there is a formula that can alternatively assign 0 or 1 based on the ID# (example highlighted in red)? I wanted to use a conditional format to highlight the row so it's easier to view for the same ID.
-
@Christina09 if your ID is in that format all the time, you could do something like
=MOD(VALUE(RIGHT(ID@row, 1)), 2)
-
Thanks for helping. Is it possible to modify the formula so it'll only show up a name that has a check on the Select column? And if none within the same ID are selected, it'll populate "Chris".
I tried modifying it to this, but it doesn't populate properly (see txt in green):
=IF(COUNTIFS(Select:Select, 1, [Column5]:[Column5], "<>", ID:ID, ID@row) >= 1, JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], "<>", Select:Select, 1, ID:ID, ID@row), ""), JOIN(COLLECT(Assignee:Assignee, Select:Select, 1, ID:ID, ID@row, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], ID:ID, ID@row))), ""))
Also, I've updated the ID number, and the color formula is not populating correctly. Is there a way to fix it?
Really appreciate your help!!
-
@Christina09 See if this works for the checkbox
=IF(IF(COUNTIFS(Assignee:Assignee, "<>Sam", [Column5]:[Column5], "<>", ID:ID, ID@row, Select:Select, 1) >= 1, JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], "<>", Assignee:Assignee, "<>Sam", ID:ID, ID@row, Select:Select, 1), ""), JOIN(COLLECT(Assignee:Assignee, Assignee:Assignee, "<>Sam", ID:ID, ID@row, Select:Select, 1, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], Select:Select, 1, ID:ID, ID@row))), "")) = "", "Chris", IF(COUNTIFS(Assignee:Assignee, "<>Sam", [Column5]:[Column5], "<>", ID:ID, ID@row, Select:Select, 1) >= 1, JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], "<>", Assignee:Assignee, "<>Sam", ID:ID, ID@row, Select:Select, 1), ""), JOIN(COLLECT(Assignee:Assignee, Assignee:Assignee, "<>Sam", ID:ID, ID@row, Select:Select, 1, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], Select:Select, 1, ID:ID, ID@row))), "")))
-
Unfortunately, it populates and joins more than one name (green txt). I was hoping it can populate the red txt in outcome column.
-
I think it's because of the dashes, let me see
-
@Christina09 if you are going to have dashes, try this
=IF(IF(COUNTIFS(Assignee:Assignee, "<>Sam", [Column5]:[Column5], "<>-", ID:ID, ID@row, Select:Select, 1) >= 1, JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], "<>", Assignee:Assignee, "<>Sam", ID:ID, ID@row, Select:Select, 1), ""), JOIN(COLLECT(Assignee:Assignee, Assignee:Assignee, "<>Sam", ID:ID, ID@row, Select:Select, 1, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], Select:Select, 1, ID:ID, ID@row))), "")) = "", "Chris", IF(COUNTIFS(Assignee:Assignee, "<>Sam", [Column5]:[Column5], "<>-", ID:ID, ID@row, Select:Select, 1) >= 1, JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], "<>-", Assignee:Assignee, "<>Sam", ID:ID, ID@row, Select:Select, 1), ""), JOIN(COLLECT(Assignee:Assignee, Assignee:Assignee, "<>Sam", ID:ID, ID@row, Select:Select, 1, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], Select:Select, 1, ID:ID, ID@row))), "")))
-
-
@Christina09 try this formula for the conditional formatting
=MOD(MATCH(ID@row, DISTINCT(ID:ID), 0), 2)
-
@Samuel Mueller You are awesome! The color formula worked!! :)
-
I modified the formula, and it's got close, but row 18 -20 is not populating "Nathan"
=IF(IF(COUNTIFS(Select:Select, 1, [Column5]:[Column5], ISNUMBER(@cell), ID:ID, ID@row) >= 1, JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], ISNUMBER(@cell), Select:Select, 1, ID:ID, ID@row), ""), JOIN(COLLECT(Assignee:Assignee, Select:Select, 1, ID:ID, ID@row, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], ID:ID, ID@row))), "")) = "", "Chris", IF(COUNTIFS(Select:Select, 1, [Column5]:[Column5], ISNUMBER(@cell), ID:ID, ID@row) >= 1, JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], ISNUMBER(@cell), Select:Select, 1, ID:ID, ID@row), ""), JOIN(COLLECT(Assignee:Assignee, Select:Select, 1, ID:ID, ID@row, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], ID:ID, ID@row))), "")))
-
@Christina09 This formula is getting out of control 😁
Try this one, and let me know if it's any better:
=IF(IF(JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], >0, ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam"), "") = "", JOIN(COLLECT(Assignee:Assignee, [Column4]:[Column4], >0, ID:ID, ID@row, Select:Select, 1, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam")), Assignee:Assignee, "<>Sam"), ""), JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], >0, ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam"), "")) = "", "Chris", IF(JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], >0, ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam"), "") = "", JOIN(COLLECT(Assignee:Assignee, [Column4]:[Column4], >0, ID:ID, ID@row, Select:Select, 1, [Column4]:[Column4], MAX(COLLECT([Column4]:[Column4], ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam")), Assignee:Assignee, "<>Sam"), ""), JOIN(COLLECT(Assignee:Assignee, [Column5]:[Column5], >0, ID:ID, ID@row, Select:Select, 1, Assignee:Assignee, "<>Sam"), "")))
-
@Christina09 just checking in, did that previous formula work? ^
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 142 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!