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

I'm trying to calculate a % value based on the CoS of tariffs
The calculation needs to work on the CoS values in the below, so trying to compute the Blank Tariff CoS value as a % of the Relevant Tariff CoS, so for example - 244,024/14,878,715 = 1.64%
Forecaster Explainability Results Clarification
We transitioned from PlanIQ to Forecaster and started importing explainability results into the model. But I’m struggling to interpret some of the since there is no information in Anapedia. Here are examples from the sales explainability results list: Historical: trend (ETS, ENSEMBLE) Historical: linear_trend (ENSEMBLE,…
ADO connection problem with “Google Cloud Storage”
I’m having a problem connecting to “Google Cloud Storage” with Anaplan Data Orchestrator. The GSP Service Account Email is correct and the GCP Private Key is correct, but it will not accept the bucket name. Please provide some guidance. Thanks.