Save the date! Join us on October 16 for our Product Ops launch event. Register here.
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:
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.
Could you please help?
Solved! Go to Solution.
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)…
Oct 04, 2020 09:47 AM
Can you change the multiple choice columns to be single select? For example, single select options:
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
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?
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'
)
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.
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)…