Make a filter but exclude one column

Question

I would like to make a filter on Google Sheet but exclude one column

For example:

In this case I would like to have the filter on Column A and B but the data on Column B should stay as they are, so when I sort A and C, B's data doesn't move.

Answer 1

Green and black filter views cannot be created with skipping columns if you wish for column overall dependency when you filter stuff. Rearranging column beforehand of creating filter views is your best option. Alternatives include giving up on filter views and filter it via formulae like QUERY and FILTER.

=FILTER({Sheet1!A:A, Sheet1!C:C}, Sheet1!A:A=D1)

=QUERY({Sheet1!A:A, Sheet1!C:C}, "where Col1='"&D1&"'", 0)

Answer 2

There is a couple of ways in which you could do what you want:

• use a re-arrange your spreadsheet so that the columns you want filtered are contiguous, create a filter view and customize the range for the columns that you want filtered
• use a query function that would filter your selection on another region (for example: query(Sheet1!A1:X15,"select A,B,C,D,H,I,J where A = 'Reagan' ",1)

I think that in-place non-continuous-region in-place filtering is not possible in Google Sheets.

Answer 3

Your example isn't great as I don't know why you don't want column B to be sorted. Is it a set of formulas that depend on a specific order (such as row 1 having =A1 and rows 2 and on being =B2+C1-A2, =B3+C2-A3, etc)? If so... You have a third option of sorting column B in the range but set up a Google Apps Script so that if any cell in column B is changed, it changes it back. Something like...

function onEdit( evt ) {
  var spreadsheet = evt.source;
  var sheet = spreadsheet.getActiveSheet();
  var editor = evt.user;
  var changeCell = [ evt.range.getRow(), evt.range.getColumn() ];
  var changeSet = [ evt.oldValue, evt.value ];
  
  Logger.log( '%s edited sheet "%s" and changed ( %s, %s ) from "%s" to "%s"', editor, sheet.getName(), changeCell[ 0 ], changeCell[ 1 ], changeSet[ 0 ], changeSet[ 1 ] );
  
  if ( changeCell[ 0 ] === 2 && changeSet[ 1 ] != changeSet[ 0 ] ) {
    sheet.getRange( changeCell[ 0 ], changeCell[ 1 ] ).activateAsCurrentCell().setValue( changeSet[ 0 ] );
  }
}