Google Sheets | Search Range for substring in referenced cell and if found, then "Y"

Dylan Payne 0
Viewed 35

I am having trouble finding a working formula to will search for the substring in a referenced cell ($A6 from Tracking 3) from the range ('AC 3'!$B4:B) and if it returns true, then a "Y" will show.

I currently have a google sheet that is used to track webinar attendance by searching the range (copied and pasted chat messages) for the referenced attendee same, and if the attendee's name is found, a "Y" is placed in that cell. The sheet is currently functional for our prior webinar platform, as when the chat messages are pasted, the attendee's name is isolated within its own cell, however, the new platform copies with the following format: "From 'attendee name' to All Panelist 00:00 PM". In the code I currently have, it is not recognized that the attendees' names are found within the ranges.

For a visual, I have "Y" input for the name Dylan Payne to show the end outcome I am looking for. I have tried the functions SEARCH, REGEXMATCH, and MATCH. I believe the SEARCH function is where my answer lies, but I'm having trouble reaching my desired outcome. If anyone is able to provide some additional feedback, it would be extremely appreciated.

I also use some google AppsScript, if there is a way to use that to isolate the attendees' names within the cell.

[Tracking 3]1

[AC 3]2

google-sheets-formula google-sheets google-apps-script
2 Answers

Add Y to range with searched for substrings

function fss() {
  const ss = SpreadsheetApp.getActive():
  const sh = ss.getSheetByName("AC3");
  const sh0 = ss.getSheetByName("Tracking");
  const sA = sh0.getRange(2,1,sh.getLastRow() - 1).getValues().flat();//names
  const rg = sh.getRange("C4:N" + sh.getLastRow());//search range
  sa.forEach((s,i) => {
    rg.createTextFinder(s).matchEntireCell(false).findAll().forEach(r => {
      r.setValue(r.getValue() + "Y")
    });
  });
}
user30hu0

I think I just found a useable code actually

=IF(ISTEXT(INDEX(FILTER('AC 3'!$B$4:$B,SEARCH($A20,'AC 3'!$B$4:$B)),1)),"Y","")

There is one more element I would like to implement from the previous code. In the previous code, it searched for the named range "courseACs", and if the title ($A$1) is found in the name range, it would have a designated number of columns needed for that webinar duration (3 columns per hour of duration). Let's say the webinar title "Ethics" is 2-hrs long and would need 6 columns for chat records. The formula then looks at the column and if it is in a column that resides after the 6th column for attentiveness tracking (Column K) then "-" will reflect in the cell. The original code is shown below.

=IF(IFERROR(E$5<=SEARCH(RIGHT($A$1,LEN($A$1)-15),courseACs,4),true),IF(OR(ISNUMBER(MATCH($A6,'AC 1'!C$4:C$2000,0))),"Y"," "),"-")

Dylan Payne0
Related