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?

Accepted answers

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))))

All comments

alec_judd

PrevContributor

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?

alec_judd

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.

PrevContributor

That did it. Thanks for the quick response.

PrevContributor

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

Quick Links

Can you help?

Unanswered questions

Management Report allows for dynamic filtering, but columns don't dynamically change
It's now possible to use the additional insights to have dynamic filters, however the columns in the table are fixed widths. It's quite unfortunate that the ability is added, but the layout isn't dynamic. I understand that a report is more fixed, but adding dynamic columns would really help make the management report…
How can i export accuracy metrics from Anaplan Forecaster to Model.
There was an error rendering this rich post.
A reminder of Bulk Copy - Current year to Prior year version for YoY analysis
A quick reminder of the Bulk Copy functionality. Bulk Copy allows you copy large volumes of data from one slice of a model to another in a single, optimised operation, instead of using formulas or imports. Use case: copy a version (RF1) into a prior year version (PY RF1) using a versions list to allow for year-on-year…