Skip to main content

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:





  1. 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.





  1. 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?

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





  1. Sheep


  2. Goats


  3. 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

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





  1. Sheep


  2. Goats


  3. 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


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?

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'


)

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'


)

Julian, you are great! 🙂



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.

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


Terms & Conditions