How to use ARRAYFORMULA + FILTER within Google Spreadsheet?

35,674

Solution 1

In Row3 please try:

=ArrayFormula(vlookup(A3:A;KEYS!A$1:B$5;2;0))

Solution 2

you can use a From spreadsheet - On change event trigger to call code like below:

function CopyPasteWastageRows() {
    var spreadsheet = SpreadsheetApp.getActive().getSheetByName("<<Sheet-Name>>");
    spreadsheet.getRange('Q2').activate();
    var currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
    currentCell.activateAsCurrentCell();
    spreadsheet.getRange('Q2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    spreadsheet.getRange('Q2').activate();
};
Share:
35,674
EnzoR
Author by

EnzoR

C/C++ programmer, PostgreSQL programmer/DBA, System and Network administrator. There are a few things that cannot really be done. And for all of them there's always documentation and room to fine a solution.

Updated on July 06, 2020

Comments

  • EnzoR
    EnzoR over 3 years

    I have a Google spreadsheet with basically a dictionary of key/value pairs. Keys are unique. In a second sheet I need to add one or more rows for every key in the dictionary with some data. In this very sheet I'd need to also report the proper value for each key.

    To do so I have tried to combine the ARRAYFORMULA function with a number of other functions on the very first row of the second sheet to avoid (if possible) the need to copy the same formula on all rows of the value column.

    So far, neither QUERY nor FILTER worked.

    QUERY requires all data, there included the arguments to the WHERE predicate, to reside on a single sheet. In my case, the filtering key would be on the second sheet while the dictionary is on the first one. So this cannot be used at all.

    FILTER seems to have a weird (to me) behavior when both used in conjunction with ARRAYFORMULA and without it.

    You can have a look to my test Google Sheet here or to snapshots here with ARRAYFORMULA (column B), without it (column C) and what I'd like to get (column D):

    KEY sheet with the key/value pairs

    DATA sheet with the data for the keys

    A little step further from pnuts' solution provides the "perfect" result without the "N/A" cells:

    =ARRAYFORMULA(IF(A3:A="";"";VLOOKUP(A3:A;KEYS!A1:B;2;FALSE)))
    

    Of course there is a major impact on the performances as the VLOOKUP is run once for every single line in in the second sheet (and this was also why I was trying to use FILTER). Those performances are quite low even with the currently linked example sheet, which is really skinny.