populate value within the same ID number with multiple criteria

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

Tags:

Best Answer

  • Samuel Mueller
    Samuel Mueller Overachievers
    Answer βœ“
    Options

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

Β«1

Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    @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
    Christina09 ✭✭✭✭✭✭
    Options

    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
    Samuel Mueller Overachievers
    Options

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

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

  • Christina09
    Christina09 ✭✭✭✭✭✭
    Options

    @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
    Samuel Mueller Overachievers
    Options

    @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
    Christina09 ✭✭✭✭✭✭
    Options

    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
    Samuel Mueller Overachievers
    Options

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

  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    @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
    Christina09 ✭✭✭✭✭✭
    Options

    @Samuel Mueller

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


  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    @Christina09 try this formula for the conditional formatting

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

  • Christina09
    Christina09 ✭✭✭✭✭✭
    Options

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

  • Christina09
    Christina09 ✭✭✭✭✭✭
    Options

    @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
    Samuel Mueller Overachievers
    Answer βœ“
    Options

    @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
    Samuel Mueller Overachievers
    Options

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

  • Christina09
    Christina09 ✭✭✭✭✭✭
    Options

    Hi @Samuel Mueller,

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

    I would not figure this formula out πŸ˜–

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!