How can I check if items in one list exist in other lists?

Frequent Contributor

How can I check if items in one list exist in other lists?

How can I check if items in one list exist in other lists that I have in a model as well?

Message 1 of 6
5 REPLIES 5
Frequent Contributor

RE: How can I check if items in one list exist in other lists?

Here is an example of how to do this: Example Lists: Employees (What I'm testing) (List A) Grades (List B) Region (List C) Products (List D) Size (List E) Steps: -Insert a new module and make it apply to the list that you're testing (List A, or for me Employees) by putting that into Pages -Take out Time -Insert line items for each of the lists that you're checking against and label them with the List name -Format each of the line items as Boolean -Pivot the module so your list (Employees) is on the rows and line items are in columns (or the other way around) -Insert this formula into the line items: IF ISNOTBLANK(FINDITEM(Grades, NAME(ITEM(Employees)))) Replace my list names with yours: List B = IF ISNOTBLANK(FINDITEM(List B, NAME(ITEM(List A)))) List C = IF ISNOTBLANK(FINDITEM(List C, NAME(ITEM(List A)))) List D = IF ISNOTBLANK(FINDITEM(List D, NAME(ITEM(List A)))) List E = IF ISNOTBLANK(FINDITEM(List E, NAME(ITEM(List A)))) This is especially useful as a workaround if you have one list that is a subset of another list and you want to check if list items are located in both areas.  Similarly, if you didn't need to know which List the items were a part of, you could combine these formulas into one and insert it into a new Boolean Line Item ("Existing?") by using OR in between the original formulas. Existing? = ISNOTBLANK(FINDITEM(List B, NAME(ITEM(List A)))) OR ISNOTBLANK(FINDITEM(List C, NAME(ITEM(List A)))) OR ISNOTBLANK(FINDITEM(List D, NAME(ITEM(List A)))) OR ISNOTBLANK(FINDITEM(List E, NAME(ITEM(List A))))
Message 2 of 6
Previous Contributor

RE: How can I check if items in one list exist in other lists?

I tried this and received a message that the code was invalid.  I am checking to see if the codes in WBS Global Studies list are in the Cost Objects list in the WBS Global Study Code property.  I used the code below: IF ISNOTBLANK(FINDITEM(Cost Objects, NAME(ITEM(WBS Global Studies))))  I also tried specifying the property to check IF ISNOTBLANK(FINDITEM(Cost Objects.WBS Global Study Code, NAME(ITEM(WBS Global Studies)))) but still received a message that the code was invalid. Can you tell me where I am going wrong?
Message 3 of 6
Frequent Contributor

RE: How can I check if items in one list exist in other lists?

Hi Ellen, It looks like I forgot to add the end of the formulas to my original post.  Can you try using the same formula you had, but just add a true/false ending? So it would look like this: IF ISNOTBLANK(FINDITEM(Cost Objects, NAME(ITEM(WBS Global Studies)))) THEN TRUE ELSE FALSE Let me know if that works out for you.
Message 4 of 6
Previous Contributor

RE: How can I check if items in one list exist in other lists?

That did it.  Thanks for the quick response.
Message 5 of 6
Previous Contributor

RE: How can I check if items in one list exist in other lists?

Alec, I believe the formula with no IF THEN ELSE like =ISNOTBLANK(FINDITEM(Cost Objects, NAME(ITEM(WBS Global Studies)))) also works fine for boolean formatted lineitems
Message 6 of 6