How to Query Function Google Spreadsheet - Data filtering based on multiple criteria (multiple columns to check at once)

Question

I have a Google Spreadsheet that contains five (5) sheets, and we are starting to use them as a basic CRM to manage tasks, projects, deadlines and customers in a very basic fashion. We assign tasks to each other in the first sheet "TaskRecords", and then we will populate the other sheets with the information there. This the google spreadsheet (with mock data):

https://docs.google.com/spreadsheet/ccc?key=0AkB1z6YLt9N_dDU0VGgyZjN5cGt2UmtRb05FbWRRZ1E#gid=6

What I would like to achieve is to filter by means of a Query function or so, to use the spreadsheet as a database and filter the following: Taks by Team Member, by Customer, by Deadline, by Project, etc, and displaying the on other sheets. I tried with Query formula: =QUERY(TaksRecords!A2:I17; "select A, B, C, D, E, F, H, I where G="AL""; 1). Where I would like to filter everything that Team Member "AL" has been assigned to do, but I'm missing something. I also tried to pull data out to other sheets using Arrayformula, but it only fetchs the cells I tell it to, and does no filtering based on multiple criteria at the same time. Furthermore, there will be new rows of data added every day.

Maybe there is some script already written that solves this, I've been testing for several days with different options but couldn't get them to work.

Any hints?

Thanks.

nhahtdh · Accepted Answer · 2015-03-12 06:31:00Z

2

Use this query

=QUERY(JOURNAL!A1:E100, "select A, B, C, D, E where col1='Fruit' and Col2='Sweet'", 1)

edited Mar 12, 2015 at 6:31

nhahtdh

56.9k15 gold badges131 silver badges164 bronze badges

answered Mar 12, 2015 at 5:56

Prabhakar Battula

213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taryn East Over a year ago

Hiya, this may well solve the problem... but it'd be good if you could edit your answer and provide a little explanation about how and why it works :) There are heaps of newbies on Stack overflow, and they could learn a thing or two from your expertise - what's obvious to you might not be so to them.

Mogsdad · Accepted Answer · 2013-09-07 21:15:05Z

1

It's just a matter of quotes. Try this, with single quotes around the name 'AL':

=QUERY(TaksRecords!A2:I17; "select A, B, C, D, E, F, H, I where G='AL'"; 1)

answered Sep 7, 2013 at 21:15

Mogsdad

46k21 gold badges166 silver badges289 bronze badges

Comments

Aaron Dahl · Accepted Answer · 2014-04-01 01:42:14Z

0

I'm no expert in this area, but I would use FILTER instead of QUERY. I use Query for a pre-defined formula to find certain records, and Filter to take a large quantity of records and narrow it down by certain defined criteria. Query is nice and clean, but it appears FILTER will suit your needs better.

answered Apr 1, 2014 at 1:42

Aaron Dahl

1

Comments

n0p · Accepted Answer · 2014-06-13 07:53:36Z

-1

How about doing a query of two conditions like:

WHERE Col1='Fruit' AND Col2='Sweet'

edited Jun 13, 2014 at 7:53

n0p

3,5062 gold badges32 silver badges53 bronze badges

answered Jun 13, 2014 at 7:34

user3736818

1

Collectives™ on Stack Overflow

How to Query Function Google Spreadsheet - Data filtering based on multiple criteria (multiple columns to check at once)

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related