Google Spreadsheet filter

0
votes

I'm trying to filter a list of employee by department in google spreadsheet

=filter(A:C,!B2:B6=D2)

my sample data is

Name    Department      age
asd     dep1            12
fds     dep2             2
plo     dep3             3
sdfsdf  dep1            56
shg     dep2            98

now I already got the filter part working.. my question is after filtering. how can I tell GSpreadsheet to only display specific column values... like use department as filter condition but only return name and age?

eventually I also want to move my result to a different sheet.

My first choice of approach for this issue was to use google macro like thingy and just hide the rows and comlumns not needed if you have suggestions for this please don't hesitate to provide.

2
filtergoogle-sheets

2 Answers

1
votes

The easiest way to filter is the [Data-Filter], otherwise [View-List] is also able to filter. Using a formula you will be displaying the results in a different column, usually on a different page.

My suggestion to omit column B would be to make two separate column filters like this:

=filter(A:A,B:B=D2) =filter(C:C,B:B=D2)

The exclamation is only necessary if you are on another sheet, you may also be trying to do something like this: filter(A2:A,B2:B="dep1") the real power of the formula is where you want to manipulate the value before your comparison like this: filter(A:A,right(B:B,1)="1") otherwise I'm not sure what you are referring to, other than Google Apps Scripts I am not aware of any functionality very similar to VBA.

If this did not answer your question can you be a little bit more specific?

0
votes

I would use the QUERY function here:

=QUERY(A:C, "select A,C where (B = 'dep1')")

This will return with two columns (Name and Age - that is "A" and "C" respectively). The filter condition is provided in the WHERE clause.

For moving the result to another sheet, I would do it another way around: Create another sheet, and then write:

=QUERY(employees!A:C, "select A,C where (B = 'dep1')")

Assuming that your original worksheet's name is "employees".

I hope it helped.