populate value within the same ID number with multiple criteria

Christina09

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!

Samuel Mueller

@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"), "")))

Samuel Mueller

@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))), ""))

Christina09

Hi @Samuel Mueller

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.

Samuel Mueller

@Christina09 if your ID is in that format all the time, you could do something like

=MOD(VALUE(RIGHT(ID@row, 1)), 2)

Christina09

@Samuel Mueller

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

Samuel Mueller

@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))), "")))

Christina09

Hi @Samuel Mueller

Unfortunately, it populates and joins more than one name (green txt). I was hoping it can populate the red txt in outcome column.

Samuel Mueller

I think it's because of the dashes, let me see

Samuel Mueller

@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

@Samuel Mueller

I updated the new formula, but it's still not populating the correct name

Samuel Mueller

@Christina09 try this formula for the conditional formatting

=MOD(MATCH(ID@row, DISTINCT(ID:ID), 0), 2)

Christina09

@Samuel Mueller You are awesome! The color formula worked!! :)

Christina09

@Samuel Mueller

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))), "")))

Samuel Mueller

@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"), "")))

Samuel Mueller

@Christina09 just checking in, did that previous formula work? ^

Christina09

Hi @Samuel Mueller,

YOU ARE A LIFE SAVER! THANK YOU SO MUCH!!!!

I would not figure this formula out 😖