Skip to main content
Solved

Mark "Yes" IF cell contains "STRING 1" or "STRING 2"

  • September 7, 2019
  • 2 replies
  • 39 views
N

I have a table “Ethnicity”. For the purpose of this post, I’ll use Asian ethnicities as an example, but my Ethnicity table will also include ethnicities of other racial groups.

On this table are entries like “Asian Indian”, “Bangladeshi”, “Bhutanese”, “Taiwanese”, “Chinese”, etc.

I have another table “Contacts” which contains a column that links to Ethnicity. Each record in Contacts can be linked to multiple ethnicities.

I now want a column that marks whether a record’s ethnicities fall under larger racial groups.

For example, if the record is linked to “Taiwanese” or “Chinese” or both, I want the record to be marked “Yes” under the column, “Asian”

What’s the best way to accomplish this?

Best answer by JonathanBowen

Hi @NYCEDU_Admin - you could do this with an IF() statement or a SWITCH() statement, e.g.:

IF(
  {Ethnicity} = 'Taiwanese', 
  'Yes', 
  IF(
    {Ethnicity} = 'Chinese',
    'Yes',
    IF(...)
  )
)

but I think this is going to get pretty complicated pretty quickly.

As Ethnicity is a linked field, a better way is to add the racial grouping to the Ethnicity table and then lookup this value back into the contacts table:

As you can see on Person 4, if you select two ethnicities, you will get two occurrences of the ethnic grouping.

If you then want a column that has a Y/N on, for example, “Asian” you could then add a formula field which works this out:

using:

IF(FIND('Asian', {Ethnic Grouping}), 'Y', 'N')

JB

2 replies

JonathanBowen
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • Answer
  • September 7, 2019

Hi @NYCEDU_Admin - you could do this with an IF() statement or a SWITCH() statement, e.g.:

IF(
  {Ethnicity} = 'Taiwanese', 
  'Yes', 
  IF(
    {Ethnicity} = 'Chinese',
    'Yes',
    IF(...)
  )
)

but I think this is going to get pretty complicated pretty quickly.

As Ethnicity is a linked field, a better way is to add the racial grouping to the Ethnicity table and then lookup this value back into the contacts table:

As you can see on Person 4, if you select two ethnicities, you will get two occurrences of the ethnic grouping.

If you then want a column that has a Y/N on, for example, “Asian” you could then add a formula field which works this out:

using:

IF(FIND('Asian', {Ethnic Grouping}), 'Y', 'N')

JB

K
C
N
N
  • NYCEDU_Admin
  • Author
  • Participating Frequently
  • September 8, 2019

Hi @NYCEDU_Admin - you could do this with an IF() statement or a SWITCH() statement, e.g.:

IF(
  {Ethnicity} = 'Taiwanese', 
  'Yes', 
  IF(
    {Ethnicity} = 'Chinese',
    'Yes',
    IF(...)
  )
)

but I think this is going to get pretty complicated pretty quickly.

As Ethnicity is a linked field, a better way is to add the racial grouping to the Ethnicity table and then lookup this value back into the contacts table:

As you can see on Person 4, if you select two ethnicities, you will get two occurrences of the ethnic grouping.

If you then want a column that has a Y/N on, for example, “Asian” you could then add a formula field which works this out:

using:

IF(FIND('Asian', {Ethnic Grouping}), 'Y', 'N')

JB


This was exactly what I needed, thank you!!!

Terms & ConditionsAccessibility statement