đźš« Is there a formula for "Not Contains"?

1

I’m trying to do a lookup of values from one list that do NOT contain the current row value (thisrow).

So I’m looking for a “NotContains” function…

I tried—
ListName.filter(Not(Contains(thisrow))
—but that did not work.

I also tried—
thisrow.Not(In(ListName))
—also did not work. :thinking:

I can get the inverse to work, displaying values from one list that DO match the value in the current row.

I just need to invert this :}

Any ideas?

2

Do you have a list of lists? :slight_smile:

Contains formula applies to the entire list, but Filter operates individual values from list. So you can use Filter(Contains(...)) only for list of lists.

Do you want to construct a list with all values excluding thisRow? If so, try Filter(@currentValue != thisRow) instead.

3

Thanks for replying — yes, i do have a list of lists :grin:

4

In this case Not(Contains(...)) should do the trick. Could you please provide some example?

5

Sure — embedded below, thanks for looking at this.

The column “Sections (dropdown select)” is where the user selects which book sections to include (from the table “Sections List” below).

The column “Omitted sections (auto)” is where I need the formula to work — I currently have “Not(Contains(…))” as the formula. (and you can see it’s showing an error.)

The next column — “Included sections (auto)” — is just to demonstrate the same exact formula — only without the “Not” — is functioning correctly.

6

Okay, you were close :slight_smile:

Change Clients.Not(Contains(...)) to Not(Clients.Contains(...))

The point notation applies to formulas which take value of type before point as one of arguments* (in this case List). These two records are equal:

Contains(Clients, Value) and Clients.Contains(Value)

However, the Not formula works with logic value, so you should first calculate logic value and only then pass it to Not. And yes, if there was NotContains formula as you suggest, this syntax would be possible: Clients.NotContains(...) :slight_smile:

*P.S. With the same logic this is also valid: Clients.Contains(...).Not()

11 Likes
7

Oh wow, that’s an interesting twist :slight_smile: I’ll have to remember that! :bulb: (and, of course, changing it as you suggested did the trick.)

I really appreciate your thorough explanation of the syntax — helps me better understand the language :clap:

Thanks for all your help!