How to use a filter inside an array formula?

Question

I have a sign-out form that I want to find out which ones are out using just formulas.

My thought process is to find the row number of the last instance of each label and if the "out" row number is higher than the "in" row number then the device is currently checked out.

Timestamp   Student     Name    Check out Chromebook    Check in Chromebook
2/26/2015   10:33:48    Bjorn   Spare1-01   
2/26/2015   10:33:59    Fred    Spare1-02   
2/26/2015   10:34:16    Bjorn                           Spare1-01
2/26/2015   10:34:39    Conor   Spare1-03   
2/26/2015   11:57:31    Conor   Spare1-01   
2/26/2015   11:57:49    Fred                            Spare1-02
2/26/2015   11:57:59    Bjorn   Spare1-02

I am able to find find out all spares that have been checked out using: =sort(UNIQUE(C2:C))

I have been able to find the row number of the last instance of each using: =max(filter(row(C:C),C:C=F2)) (my sort unique function is in column F).

I would like to get my last row formula to work with an arrayformula so that I don't have to rely on manually dragging the formula down.

For example I would like this to work: =arrayformula(max(filter(row(C:C),C:C=F:F))) note the addition of arrayformula and the change from F:2 to F:F.

I am not quite sure why but I get a result of 1000.

Edit: My expected output would be "Last checkout row".

Out Unique  Last checkout row
Spare1-01   6
Spare1-02   8
Spare1-03   5

If I can figure out the adding it to the array formula I would also like to find a way to put in the sort (unique function so that it becomes a single formula which I enter in the column number and it spits out the last row numbers of each unique term).

Here is a link to a document that I am playing with: https://docs.google.com/spreadsheets/d/1jC0RPxUZSt7BCHRQI5vBXvoANbQlu9CjDNr2VV-MOOI/edit?usp=sharing

Edit (final formula used) (I can't thank @JVP enough for the help with this):

=ArrayFormula( if(vlookup(unique(filter(C2:C, len(C2:C))), sort({C2:C, A2:B, row(A2:A)},4,0), 4, 0) > iferror(vlookup(unique(filter(C2:C, len(C2:C))), sort({D2:D, A2:B, row(A2:A)},4,0),4,0),0), vlookup(unique(filter(C2:C, len(C2:C))), sort({C2:C, A2:C, row(A2:A)},5,0), {2,4,3}, 0), ))

That would be why I am getting 1000, but I still don't know why it is not logically working. — Bjorn Behrendt, Feb 26 '15 at 20:02
I want this formula to work: =arrayformula(max(filter(row(C:C),C:C=F:F))) — Bjorn Behrendt, Feb 26 '15 at 20:08
pnuts, I edited my last post to include a desired output. I can get it using the max(filter(row formula and copying that down, but I would like to have it part of an arrayformula so that it can be more dynamic. — Bjorn Behrendt, Feb 26 '15 at 20:15

JPV · Accepted Answer · 2015-02-26T21:53:34.990

3

For your desired output (including the names), try:

=ArrayFormula({unique(filter(C2:C, len(C2:C))), vlookup(unique(filter(C2:C, len(C2:C))), sort({C2:C, A2:B, row(A2:A)},4,0), 4, 0)})

Following your thought process (ultimately you want to see if a device is currently checked in or out, right ?), try:

=ArrayFormula({unique(filter(C2:C, len(C2:C))), if(vlookup(unique(filter(C2:C, len(C2:C))), sort({C2:C, A2:B, row(A2:A)},4,0), 4, 0) > iferror(vlookup(unique(filter(C2:C, len(C2:C))), sort({D2:D, A2:B, row(A2:A)},4,0),4,0),0), "checked out", "checked in")})

Example sheet with last formula in F2

edited Feb 26 '15 at 21:53

answered Feb 26 '15 at 21:08

JPV

26,499
4
33
48

1

@pnuts: you should see the list with uniques and their current status: checked out or checked in. Check out the sheet I added to my answer.. – JPV Feb 26 '15 at 21:52
1

Just my interpretation of what OP try to achieve. So no guarantees ... ;-) – JPV Feb 26 '15 at 22:16
I am trying to break this down. What do the {} do inside the arrayFormula do. I couldn't find it in the documentation. – Bjorn Behrendt Mar 04 '15 at 15:58
@JVP, I am really trying hard to do this myself, but I cannot figure out the changes to your formula to make it work. My goal is to only show chromebooks that are out, along with the corresponding time-stamp and name. – Bjorn Behrendt Mar 04 '15 at 16:35
@BjornBehrendt. In the example sheet I added a formula in I2 (not thoroughly tested though). See if that works for you. The {} are used to construct a literal array. See sheet 2 for some simple examples.. I hope this helps ? – JPV Mar 04 '15 at 19:04
@JVP, works like a charm. In your most recent example you changed the last sort to 5,0 instead of the others which used 4,0 - could you explain why this one was different. Also, you entered {2,4} which I realize shows the columns that I want to see (My final version I changed to {2,4,3} so that it includes the name). My question is why do the column numbers appear to be one off, where 2 refers to column A? – Bjorn Behrendt Mar 04 '15 at 21:25
@BjornBehrendt: It all depends on the array that is used. Consider the arrays in {} as 'virtual arrays'. E.g: in the array {D2:D, A2:B, row(A2:A)}, 1st col is D, 2nd col is A, 3rd is B and 4th would be a newly created column consisting in the output of row(A2:A). On the other hand in the added array in the new formula {C2:C, A2:C, row(A2:A)},5,0) there is a column extra: A2:C instead of A2:B. So in order to be able to sort by row number, the number is incremented with 1. Does that help ? – JPV Mar 04 '15 at 21:55
that makes sense. Thank you for your help. I will be using this formula in my Form Add-on CheckITOut. I will be sure to credit you as the creator of the formula on the site. – Bjorn Behrendt Mar 05 '15 at 02:33
Thanks, being a Add-on user myself, I'm glad I could contribute to such a project. Maybe one day I'll start to write an Add-on myself and then I can use some of your expertise. :-) – JPV Mar 05 '15 at 07:49

score 0 · Answer 2 · answered Mar 13 '15 at 01:59

Thank you @JPV Here is the final formula that I used, I changed things around so that all I had to do was enter in the number of the column in and number of the column out:

=sort(ArrayFormula( if(vlookup(unique(filter(INDIRECT("'Form Responses 1'!"&"R2C4:C4", FALSE), len(INDIRECT("'Form Responses 1'!"&"R2C4:C4", FALSE)))), sort({INDIRECT("'Form Responses 1'!"&"R2C4:C4", FALSE),row('Form Responses 1'!A2:A)},2,0), 2, 0) > iferror(vlookup(unique(filter(INDIRECT("'Form Responses 1'!"&"R2C4:C4", FALSE), len(INDIRECT("'Form Responses 1'!"&"R2C4:C4", FALSE)))), sort({INDIRECT("'Form Responses 1'!"&"R2C5:C5", FALSE),row('Form Responses 1'!A2:A)},2,0), 2, 0),0), vlookup(unique(filter(INDIRECT("'Form Responses 1'!"&"R2C4:C4", FALSE), len(INDIRECT("'Form Responses 1'!"&"R2C4:C4", FALSE)))), sort({INDIRECT("'Form Responses 1'!"&"R2C4:C4", FALSE), row('Form Responses 1'!A2:A), INDIRECT("'Form Responses 1'!"&"R2C1:C"&COUNTA('Form Responses 1'!1:1)&"", FALSE)},2,0), {3,4,6}, 0), )))

I am programably creating the script, and here is the code.

function createReportSheet(setName) {
  if (!setName){setName = "Items"};
  var destId = FormApp.getActiveForm().getDestinationId();
  var as = SpreadsheetApp.openById(destId);
  try{
  var sheet = as.insertSheet("Report Out: "+setName);
  } catch(err){
   var sheet = as.getSheetByName("Report Out: "+setName);
   sheet.clear(); 
  }  


  //build formula
  var formSheetName = "'Form Responses 1'!";
  var colOut = 4;
  var colIn = 5;
  var colToShow = "3,4,6"; //  A=3, B=4, C=5, .. 

  var frTimeStamp = formSheetName+"A2:A";
  var lastCol = "&COUNTA("+formSheetName+"1:1)&";

  var frSort = 'INDIRECT("'+formSheetName+'"&"R2C'+1+':C"'+lastCol+'"", FALSE)';
  var frIn  = 'INDIRECT("'+formSheetName+'"&"R2C'+colIn+':C'+colIn+'", FALSE)';
  var frOut = 'INDIRECT("'+formSheetName+'"&"R2C'+colOut+':C'+colOut+'", FALSE)';

  //break into parts
  var f_unique = "unique(filter("+frOut+", len("+frOut+")))";
  var f_sortIn = "sort({"+frOut+",row("+frTimeStamp+")},"+2+",0)";
  var f_vlookupIn = "vlookup("+f_unique+", "+f_sortIn+", "+2+", 0)";
  var f_sortOut = "sort({"+frIn+",row("+frTimeStamp+")},"+2+",0)";
  var f_vlookupOut = "vlookup("+f_unique+", "+f_sortOut+", "+2+", 0)"; 
  var f_sortDisp = "sort({"+frOut+", row("+frTimeStamp+"), "+frSort+"},"+2+",0)";
  var f_vlookupDisp = "vlookup("+f_unique+", "+f_sortDisp+", {"+colToShow+"}, 0)"; 
  var formula1 = "ArrayFormula( if("+f_vlookupIn+" > iferror("+f_vlookupOut+",0), "+f_vlookupDisp+", ))";
  var formula = "=sort("+formula1+")";

  // put formula into sheet
  sheet.getRange(2, 1).setFormula(formula);
  sheet.getRange("a:a").setNumberFormat("mm/dd/yy h:mm:s");

}

How to use a filter inside an array formula?

2 Answers2

Linked