Index, Collect, Contains, Or

JSpears ✭✭✭✭✭
edited 05/15/24 in Formulas and Functions

In the source sheet that I'm referencing, there are 2 rows that contain the assignments, and I want both names.

Here is the source sheet. So for the row in the target sheet where I am pulling the assignments for "Nursing Go-Live" I want both names (Greg and Darla):

I want it to pull in the name for "Nursing Go-Live" that is not already in the 2nd column. So using the source sheet above (one row for Greg and one row for Darla for the "Nursing Go-Live" task) and on my target sheet below, where it shows Darla in the first and second column, I want it to just pull Greg into the first column because Darla is already in the second column.

=IFERROR(INDEX(COLLECT({KITasks_EmpName}, {KITasks_ProjCode}, [KI Proj Code]@row, {KITasks_TaskDescription}, CONTAINS(KeyWord@row, @cell), {KITasks_EmpName}, OR(@cell <> "Ericka XXXXX (B02161)", @cell <> [Nursing PM Shift Resource]@row)), 1), "")

I am NOT getting any errors, but it's not doing exactly what I want. As you can see in the screenshot above - Darla is in the first column and the second column.

I had this working when I was NOT including the OR and just had that it couldn't pull in Ericka XXXX (B02161) (that is a placeholder assignment that I want to ignore). But I need to add that it also can't pull in a name that is already in the Nursing PM Shift Resource column.

I'm thinking there may need to be an IF or two…..IF Nursing PM Shift Resource is blank then I just don't want it to include any "Ericka XXXXX (B02161)", if Nursing PM Shift Resource is NOT blank, then I don't want it to pull in any "Ericka XXXXX (B02161)" or the same name that is in the Nursing PM Shift Resource.

So I tried this: =IF(ISBLANK([Nursing PM Shift Resource]@row), INDEX(COLLECT({KITasks_EmpName}, {KITasks_ProjCode}, [KI Proj Code]@row, {KITasks_TaskDescription}, CONTAINS(KeyWord@row, @cell), {KITasks_EmpName}, @cell <> "Ericka Gardner (B02161)"), INDEX(COLLECT({KITasks_EmpName}, {KITasks_ProjCode}, [KI Proj Code]@row, {KITasks_TaskDescription}, CONTAINS(KeyWord@row, @cell), {KITasks_EmpName}, OR(@cell <> "Ericka Gardner (B02161)", @cell <> [Nursing PM Shift Resource]@row)))

But I'm getting "INCORRECT ARGUMENT SET" on rows where Nursing PM Shift Resource is blank, and where Nursing PM Shift Resource is NOT blank, no name is entered in the KI Assignment column.




  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @JSpears

    Try changing OR to AND in your "OR(@cell <> "Ericka XXXXX (B02161)", @cell <> [Nursing PM Shift Resource]@row)" formula.

    With the criteria, Darla meets the first condition because she is not "Ericka XXXX (B02161)."

    So, the condition is giving Darla and Greg. I do not understand why you get Greg instead of Darla with the INDEX({range},1). In my simplified example, I get Greg with INDEX 1. Some other COLLECT criteria may have put Darla earlier, or you may have other rows with Darla before Greg.

    To get your desired result, I would change the condition to AND(@cell <> "Ericka XXXXX (B02161)", @cell <> [Nursing PM Shift Resource]@row). Greg is the only Assignment that is neither Ericka nor [Nursing PM Shift Resource]@row.

  • JSpears
    JSpears ✭✭✭✭✭

    Thank you @jmyzk_cloudsmart_jp - here is the issue that I'm running in to:

    Here is my original formula:

    =IFERROR(INDEX(COLLECT({KITasks_EmpName}, {KITasks_ProjCode}, [KI Proj Code]@row, {KITasks_TaskDescription}, CONTAINS(KeyWord@row, @cell), {KITasks_EmpName}, OR(@cell <> "Ericka XXXX (B02161)", @cell <> [Nursing PM Shift Resource]@row)), 1), "")

    Source Sheet:

    These are 5 rows all for the same project code. (My source sheet has more than 100 rows for over 75 different projects and at least 20 unique assignment names in Employee Name)

    Target Sheet:

    These are the 4 rows for that same project code that I want the above assignments to fall in to.

    Only the "Nursing Go-Live" tasks will have more than 1 person assigned (and more than one row in the source sheet).

    I want to pull Employee Name into KI Assignment when the Nursing PM Shift Resource cell is BLANK.

    When the Nursing PM Shift Resource cell is NOT blank, the name that I want to pull into the KI Assignment column is the one that is NOT the same as the Nursing PM Shift Assignment.

    So in the example above, on the Super User task it should just show Darla. On the Pharmacy task it should just pull Gay. But on the Nursing Go-Live task, the KI Assignment should be Darla because Cecilia is already in the Nursing PM Shift Resource.

    When I replace the OR statement with an AND statement

    =IFERROR(INDEX(COLLECT({KITasks_EmpName}, {KITasks_ProjCode}, [KI Proj Code]@row, {KITasks_TaskDescription}, CONTAINS(KeyWord@row, @cell), {KITasks_EmpName}, AND(@cell <> "Ericka XXXX (B02161)", @cell <> [Nursing PM Shift Resource]@row)), 1), "")

    I get this below. I lose the assignments for Super User and for Pharmacy:

    Thank you for your help!

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭


    Thank you for the extensive explanation and for providing real-world examples. This helps a lot!😁

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!