Skip to main content

I have a tab with a list of hospitals. Some of them have the tag “COE”.

On an other tab, I’m listing the feedback gathered from hospitals.


Some feedback came from many different hospitals.

So I add the name of the hospitals on the column “requesters”


Now, I want to sort my list of feedback, and put on the top the feedback coming from “COE” hospitals.


How can I do that?


I thought to use a formula to have “COE” on the name of my hospital, and do something like “IF on the requester list there is an hospital with “COE” on it, then check the box” (for example).

But I have NO IDEA how do do this… and there is maybe an even easier way to do it…


Thank you very much for your help!

What about something like this?


IF(SEARCH("COE", {requesters}), "COE")

What about something like this?


IF(SEARCH("COE", {requesters}), "COE")

It works! Thank you very much!!!

And just 2 more questions (because I really have a lot of trouble with the formula:



  1. How does it looks like if I have 2 different columns of requesters?

  2. How does it looks like, if on the top of that, instead of writing “COE” on my column, I want to write the number of "COE"s detected on my 2 requesters columns?


Thank you VERY much!

It works! Thank you very much!!!

And just 2 more questions (because I really have a lot of trouble with the formula:



  1. How does it looks like if I have 2 different columns of requesters?

  2. How does it looks like, if on the top of that, instead of writing “COE” on my column, I want to write the number of "COE"s detected on my 2 requesters columns?


Thank you VERY much!


Well, those formulas would be much more complicated.


This formula will tell you the number of "COE"s in a field.


(

  LEN({requesters}) 

  -

  LEN(

    SUBSTITUTE(

      {requesters},

      "COE",

      ""

    )

  )

)

/ 3

If you have multiple fields, you can have an even more complicated formula. Here I have collapsed the above formula to save space. You would need to edit the name of requesters2 to match your actual field value.


((LEN({requesters}) - LEN(SUBSTITUTE({requesters},"COE","")))/3)

+

((LEN({requesters2}) - LEN(SUBSTITUTE({requesters2},"COE","")))/3)

Well, those formulas would be much more complicated.


This formula will tell you the number of "COE"s in a field.


(

  LEN({requesters}) 

  -

  LEN(

    SUBSTITUTE(

      {requesters},

      "COE",

      ""

    )

  )

)

/ 3

If you have multiple fields, you can have an even more complicated formula. Here I have collapsed the above formula to save space. You would need to edit the name of requesters2 to match your actual field value.


((LEN({requesters}) - LEN(SUBSTITUTE({requesters},"COE","")))/3)

+

((LEN({requesters2}) - LEN(SUBSTITUTE({requesters2},"COE","")))/3)

It works!! You’re the best, thank you very much!! 🎉

Reply


Terms & Conditions