Index Collect + If Contains Multiple Partial Terms in Column

Lori Flanigan

I am trying to write a formula to pull the employee name (Full Name) from my source sheet into my target sheet if these criteria are met:

·        Employment Status field in source sheet lists Active or Pending for the employee

·        Company field in source sheet lists Company X or is blank for the employee

·        Job Profile field in source sheet lists “Clerk” or “Admin” as part of the Job Profile term for the employee

Source sheet setup:

Full Name               Job Profile       Employment Status     Company

Anderson, Jane              Clerk III               Active                                     Company X

Bell, Mike                        Clerk II               Active                                     Company X

Smith, Joe                      Clerk I               Pending                                  Group Y

Jones, Sara                    Manager            Active                                     Company X

Drake, Henry                  Admin I            Termination – Completed      Group Y

Lomax, Kay                    Clerk I – Bilingual   Active                                 Company X

Franklin, Steve               Admin II                  Pending                               

Target sheet setup:

Full Name              Job Profile     Employment Status        Company         Type  Value  Date

INDEX/COLLECT is the formula type I'm familiar with to pull the name, but I cannot find an example of adding an IF/CONTAINS with it. Am I even on the right track?

I appreciate any help provided.

Lori Flanigan

Kelly Moore

Hey Lori

As is, you have nothing in your target sheet to differentiate your data row by row for the formula to match to. For instance, Emp ID would differentiate the rows. A JOIN could give you all the data but will lump it into one cell, which isn't what I think you are looking for. Do you have access to DataMesh where you could use Paul's row summary report as the data source? DataShuttle, with an export of the source sheet and import to Target might also work. You could use copy row from the source sheet to your target sheet and hide/add columns as needed on the Target. Paul might know otherwise, I haven't found other alternatives that keep a dynamic range in sync with another sheet.

Kelly

Kelly Moore

Hey @Lori Flanigan

You should be able to use {Source sheet Job Profile}, OR(CONTAINS("Clerk", @cell), CONTAINS("Admin", @cell)) within your COLLECT function

=INDEX(COLLECT({source sheet Full Name), {Employment Status}, OR(@cell="Pending", @cell="Active"), {Source sheet Company}, OR(@cell="Company X", @cell=""), {Source sheet Job Profile}, OR(CONTAINS("Clerk", @cell), CONTAINS("Admin", @cell))),1)

Does this work for you?

Kelly

Lori Flanigan

Thank you, Kelly! The formula works, but only one name is coming over instead of the 125 that should come over when I convert to a column formula.

Paul Newcome

Have you looked into creating a row report?

Lori Flanigan

Hi Paul,

No, a report won't work in this situation. This formula would pull names into a sheet being used to track some employee data, and I would like the names to dynamically pull in because the staff changes often.

Lori

Paul Newcome

Is there a reason you cannot put those columns on the source sheet?

Lori Flanigan

Yes, it's a separate process that is set up for a specific purpose.

Kelly Moore

Hey Lori

As is, you have nothing in your target sheet to differentiate your data row by row for the formula to match to. For instance, Emp ID would differentiate the rows. A JOIN could give you all the data but will lump it into one cell, which isn't what I think you are looking for. Do you have access to DataMesh where you could use Paul's row summary report as the data source? DataShuttle, with an export of the source sheet and import to Target might also work. You could use copy row from the source sheet to your target sheet and hide/add columns as needed on the Target. Paul might know otherwise, I haven't found other alternatives that keep a dynamic range in sync with another sheet.

Kelly

Paul Newcome

It is possible, but it is also a little on the complex side.

You will need an auto-number column on the source sheet (no special formatting) and then a text/number column with the following column formula:

=IF(AND(OR(Employment Status]@row = "Pending", [Employment Status]@row = "Active"), OR(Company@row = "X", Company@row = ""), OR(CONTAINS("Admin", [Job Profile]@row), CONTAINS("Clerk", [Job Profile]@row)), [Auto-Number Column Name]@row)

From there your second sheet would need a text/number column with the numbers 1 through however many you think you will need manually entered. If you think you will need 25, go ahead and enter 1 - 40. Basically you want to give yourself a buffer beyond the maximum expected.

Then you would use this to pull in the names:

=INDEX({Source Sheet Name Column}, MATCH(Number@row, {Source Sheet Helper Column}, 0))

Lori Flanigan

Thanks to you both! I created a row report, as Paul suggested. That report is the source sheet in Data Mesh to dynamically update the names in the target sheet. I've shown my team the draft and they are thrilled with it.

Lori

Kelly Moore

Great! That's exactly how I typically end up doing it- and always from a report as the source.

Glad you found a solution