CODEWITHSUNDEEP
google-sheets
Jake
Jake

Reputation: 173

Verifying multiple string match from range of cells

I am using google sheets. My sheet is for scheduling students. There are multiple teachers that teach a subject each hour, and I want to validate a student's schedule by ensuring they have each of the four required classes preset. Math, ELA, Science, and Social Studies. As the example below shows, the scheduler chooses from a drop down list of available classes for each hour. The cell contains the teacher's name and the subject taught. The Validation cell should search across the row for each of the four partial strings "ELA", "Math", "Science", "Social Stud" and return "Good" if all four are present, or "Error" if there is a duplicate, or one missing. Any ideas?

Google sheet image

Upvotes: 1

Views: 959

Answers (2)

Doomd
Doomd

Reputation: 1406

If there is any chance you might change your strings (Math, ELA, etc), I would recommend declaring them as named ranges, and then you wouldn't have to change all the formulas if you change your strings.

I created an example sheet here: https://docs.google.com/spreadsheets/d/1P9VjIWLQBm9D75caNI1GH4US9buubPj7LpKFe8vLw2M/edit?usp=sharing

Here's the formula I used (though it performs the same basic function as your accepted answer without requiring wildcards):

=if(isnumber(sum(SEARCH(string1,join(" ",B2:E2)),SEARCH(string2,join(" ",B2:E2)),SEARCH(string3,join(" ",B2:E2)),SEARCH(string4,join(" ",B2:E2)))),Good,Bad)

I'm using named ranges for the valid or error responses as well ("Good" & "Bad"). Again, named ranges allow you to change the string on your "Variables" sheet without having to alter the formulas in your Validation cells

Upvotes: 1

Liora Haydont
Liora Haydont

Reputation: 1283

You could simply use AND in a IF condition

=if(
   AND(
      countif(A3:F3,"*ELA")=1,
      countif(A3:F3,"*Math")=1, 
      countif(A3:F3,"*Social")=1,
      countif(A3:F3,"*Science")=1,
   ),
   "Good",
   "Error")

if you have other validation you can add it to the AND or even add a AND/OR confition inside a condition

Upvotes: 0

Related Questions