Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Big Challenge: Filtering in Columns with Multiple ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
2711
5

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 04, 2020 03:21 AM

Hi, all!

I’m facing a challenge to do the following:

Column A with multiple choices (All, Sheep, Goats… up to 12 more)

Column B with multiple choices (Universal, Black, White)

Column C with single choice (F, SE)

Column D with multiple choices (3 months, 6 months, 9 months)

Because I have more than 500 records I need a formula to do the same as filtering manually.

May be the topic should be divided in two parts:

- I want to have a formula that can tell me if a certain combination for a record is “true”. If it is true, we can name it (All-Sheep-Black-F-3-months), then the formula displays the name of the combination (All-Sheep-Black-F-3-months).

I found that in excel this could be done with ISNUMBER and FIND but Airtable doesn’t support ISNUMBER.

The problem for me here is that I cannot use AND and OR in columns with multiple choices because it doesn’t work or I just can’t make it work.

For combination All-Sheep-Universal-Black-F-3-months this is what I use:

IF(

AND(

OR({Column A}=“All”, {Column A}=“Sheep”),

OR({Column B}=“Universal”, {Column B}=“Black”),

{Column C}=“F”,

{Column D}=“3 months”),

“All-Sheep-Universal-Black-F-3-months”)

You can see the record in the attached printscreen.

In this case the formula should return for the two records “All-Sheep-Universal-Black-F-3-months”, but that work only if the columns are with single choice.

- I need this formula to check for about 77 combinations like that, so can you suggest any way to do it. The thing is that the record could be true for many combinations and I want all of them to be displayed (for example with comma) and after that to filter them manually in Column E with filter in the Table View.

Could you please help?

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 04, 2020 12:57 PM

The formula I used for the certain case. As Julian suggested I combined AND and OR with FIND.

SO here is the formula:

```
IF(
AND(
OR(FIND(
'All',
{Column A}
),
FIND(
'Sheep',
{Column A}
)),
OR(FIND(
'Black',
{Column B}
),FIND(
'Universal',
{Column B}
)),
FIND(
'F',
{Column C}
),
FIND(
'3 months',
{Column D}
)
),
'True',
'False'
)
```

Now for the other combinations I will use IF functions such as IF(1st combination)&IF(2nd combination)…

Reply

5 Replies 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 04, 2020 09:47 AM

Can you change the multiple choice columns to be single select? For example, single select options:

- Sheep
- Goats
- Goats and Sheep

If you were to do that, it would be much easier to concatenate the four columns into a string, like this:

`CONCATENATE(Column A, Column B, Column C)`

Then you could filter, or set up some kind of formula based on the unique strings

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 04, 2020 10:23 AM

Hi, Julian!

Thank you very much for your suggestion!

Actually there are a lot of combinations between these 12 items in Column A, so I don’t think this is applicable.

Do you have any other suggestions?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 04, 2020 11:39 AM

This topic contains a solution that I think is closer to what you’re looking for:

So it would look something like this:

```
IF(
AND(
FIND(
'All',
{Column A}
),
FIND(
'Sheep',
{Column A}
),
FIND(
'Black',
{Column B}
),
FIND(
'F',
{Column C}
),
FIND(
'3 months',
{Column D}
)
),
'True',
'False'
)
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 04, 2020 12:14 PM

Julian, you are great! :slightly_smiling_face:

It works like a charm.

Could as well suggest how to structure IF functions as I want in column E to put multiple checks for true and false?

As I said earlier I need to write the formula for 77 such filtered combinations.

The problem: One record can have more than one IF function with True statement, so putting next IF functions in False statement will not work.

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 04, 2020 12:57 PM

The formula I used for the certain case. As Julian suggested I combined AND and OR with FIND.

SO here is the formula:

```
IF(
AND(
OR(FIND(
'All',
{Column A}
),
FIND(
'Sheep',
{Column A}
)),
OR(FIND(
'Black',
{Column B}
),FIND(
'Universal',
{Column B}
)),
FIND(
'F',
{Column C}
),
FIND(
'3 months',
{Column D}
)
),
'True',
'False'
)
```

Now for the other combinations I will use IF functions such as IF(1st combination)&IF(2nd combination)…

Reply