CODEWITHSUNDEEP
arrayssortinggoogle-sheetsgoogle-sheets-formulavlookup
Beee
Beee

Reputation: 49

Index Match to Return Second To Last Non-Numerical Entry

My team has to fill in their schedules in google forms each week, and basically I'm trying to do it so if they select "Maintain," I can automatically pull their last/previously filled entry.

So far I've gotten this: =if(D23="Maintain",(INDEX('Form Responses'!$E$2:$R,MATCH($C23,'Form Responses'!$B$2:$B)-1)),(INDEX('Form Responses'!$E$2:$R,MAX(IF('Form Responses'!$B$2:$B=C23,ROW(C:C)))))) but the "True" section isn't returning the right row (with or without the "-1").

The name "JC" is supposed to show the data from row32 in the responses sheet, and "NA" is supposed to return that of row40.

The "-1" is supposed to Index/Match the second-to-last entry with that specific name, but I can't tell if it's working correctly or not.

What am I doing wrong with the "True" portion?

Sample sheet: https://docs.google.com/spreadsheets/d/1FeYzXH3spqKqZAZWpKO0PFMdYuTjrtDoZIxlW50B-ns/edit#gid=1728521224

Upvotes: 1

Views: 52

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15308

Another solution that saves you from duplicating your database.

When a new response is submitted, if Maintain is specified, this function will retrieve the last response for the same Name and duplicate the data already entered. Doing this will allow you to maintain during for consecutive days.

// this function needs to be triggered
function onFormSubmit(e) {
  var responses = e.namedValues;
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  if (responses["Maintain/New"] == 'Maintain') {
    var data = sheet.getDataRange().getValues().filter(r => r[1] == responses["Name"]).sort(function (a, b) {
      return b[0] - a[0];
    });
    if (data.length > 1) {
      data = data.splice(1, 1).flat()
      data.splice(0, 3)
      sheet.getRange(row,4,1,data.length).setValues([data])
    }
  }
}

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

delete everything in E3:R range and use in E3:

=ARRAYFORMULA(IFNA(IF(D3:D="Maintain", 
 VLOOKUP(C3:C, QUERY(SORT('Form Responses'!B2:R, ROW('Form Responses'!B2:B), 0), 
 "where not Col2 = 'Maintain' and Col1 is not null", 0), COLUMN(D:Q), 0),
 VLOOKUP(C3:C, SORT('Form Responses'!B2:R, ROW('Form Responses'!B2:B), 0), COLUMN(D:Q), 0))))

enter image description here

Upvotes: 1

Related Questions