Google Sheets – Filtering a List by Value of Offset Column

google sheets

If I have a sheet laid out similar to the below:

      | T1 | T2 |   
one   | X  |    |
two   | X  |  X |
three |    |  X |

If there a way i can make a dynamically updated list of values from the first column where they have an X in the column offset by (say) one column from their own value ?

So in this case one list that'd be 'one and two' and one that'd include 'two and three'

Something like; =FILTER(A2:A5, OFFSET("C[1]") == "X") ?

Up to now I've been using the funnel filter tool on the column built into the app, which works for smaller item numbers – but really i'd like to be able to have each filtered output on it's own worksheet.

Best Answer

QUERY function could be what you are looking for.

=QUERY(A2:C4,"select A where B='X' label A ''")

and

=QUERY(A2:C4,"select A where C='X' label A ''")

Related Solutions

Google Sheets – Filter High Score List for Personal Bests

For the first column, in column D for example, use the following formula:

=UNIQUE(A1:A5)

and the second column, in column E for example:

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D;A:B;{2}*SIGN(ROW(A:A));0),""))

Google-sheets – How to change cell value, looping through a provided list and record result in Google Sheets

You need an Apps Script for this (under Tools > Script Editor). Here is how it could work (explanation below).

function loop() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');   // name of your sheet
  var data = ss.getSheetByName('data').getRange('A1:A20').getValues();
  var output = [];
  for (var i = 0; i < data.length; i++) {
    sheet.getRange('B1').setValue(data[i][0]);
    SpreadsheetApp.flush();
    Utilities.sleep(10000);
    if (sheet.getRange('B18').getValue() == 'Yes') {
      output.push([data[i][0]]);
    }
  }
  sheet.getRange(2, 12, output.length, 1).setValues(output);
}

The lines until for are just setting up pointers to resources and grabbing data. In the loop, the value in B1 is set; the spreadsheet is flushed to make sure it really applies; and then the script waits 10 seconds (10000 milliseconds) for the financial data to arrive. You may decide to change this pause depending on your sheet's performance. If the value in B18 is Yes, the stock symbol is added to array "output". After the whole thing has ran, the output is placed in column L (column number 12).

To be able to run this function from the spreadsheet itself, add the function below to the script, which will create a menu item for it.

function onOpen() {
   var menu = [{name: "Run a loop", functionName: "loop"}];
   SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);  
}

(Another option is to use a clickable drawing).

Best Answer

Related Solutions

Google Sheets – Filter High Score List for Personal Bests

Google-sheets – How to change cell value, looping through a provided list and record result in Google Sheets

Related Topic