Index, Collect, Contains, Or

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.

Jennifer

Find more posts tagged with

Formulas

Accepted answers

All comments

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.

https://app.smartsheet.com/b/publish?EQBCT=83f45ba605564f519a22b4fe67e03be8

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

@JSpears

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

JSpears

OK - so I'm thinking the simplest solution may be:

If KI Assignment does not equal Nursing PM Contact, then 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)), 1)

Can someone help me with the syntax here?

=IF({KITasks_EmpName} <> [Nursing PM Contact]@row), 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)), 1), "")

JSpears

@Paul Newcome - Seriously awesome Smartsheet formula warrior - can you help me, please?

This is my Source Sheet: I export a spreadsheet from another program and copy and paste the content from the Excel file into my sheet. The Excel file has almost 500 rows, and it grows weekly. I do this daily. (We don't have any advanced functionality—just core Smartsheet.)

As you can see, for Facility 1 (yellow rows) there are 2 assignments, Dawn and Darla.

And here is my Target Sheet:

For Facility 1 (yellow row), I have manually assigned Dawn as the evening shift resource. So, in the KI Assignment column, I want it to see that Dawn is already assigned in the Nursing PM Shift Resource and enter the next Employee Name for the Nursing Go-Live task, who would be Darla.

This is the formula that I'm using:

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

You can see that for Facility 2 (Green rows), the formula is working because I told it not to enter the name if the name is "Ericka XXXX (B02161)." But for the other two facilities, I can't get the formula to pass the first row it finds if that name is already in the Nursing PM Shift Resource column. The formula always stops on the first name it finds.

Right now, I have to manually go to my source sheet and drag the row that SHOULD be displayed in the KI Assignment, above the row with the name that is in the Nursing PM Shift Resource. However, I have almost 500 rows in my source sheet, so this is really getting burdensome. I will be adding at least 500 more rows over time, so if you can help, I would greatly appreciate it.

Thank you in advance for your amazing problem-solving minds.

Jennifer

Paul Newcome

@JSpears Try changing the 1 at the end to a 2. That number there is what tells the INDEX function which entry to pull out of the list created by the COLLECT function.

If your collect functions ends up outputting a list that looks like this:

The number at the end of your INDEX/COLLECT tells it which letter to pull out of that list. So the 1 would output the "A". Changing it to a 3 will output "C".

Although… The fact that your formula specifically tells it to exclude the string in the [PM Shift] column, but it is still grabbing it leads me to believe there may be an issue with your strings. What happens if you copy/paste Dawn's data from the first sheet into the second? Does that fix it?

JSpears

Hi Paul - When I changed the 1 at the end to a 2, it cleared out that column completely and returned nothing.

Right now, with this as the formula:

=IFERROR(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)), 1), "")

Source:

Target:

If I change the formula - (change the 1 to a 2) :

=IFERROR(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)), 2), "")

If I change the formula - (OR to AND) :

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

If I change the formula - (Keep AND and change 1 to 2) :

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

I lose all rows - all rows have NO KI Assignment

Paul Newcome

Glad it is working for you now.

JSpears

Sorry @Paul Newcome - I was wrong - I edited my previous statement above - I was so focused on the Nursing rows that I failed to realize that while Nursing rows were correct, I had lost the assignments for all other rows.

Paul Newcome

Lets go back to this one:

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

and remove the IFERROR so we can see what's going on…

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

JSpears

@Paul Newcome - Here is what I get with this formula

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.