Google Spreadsheet Group By Query

Question:

( by Hekman Millan )

Chrome/Mac OSX 10.7.5

Ok pals, first of all, I want you to know that I appreciate a lot the time you take to help me on this. Thank you (A lot)

I have two sheets, one named 'Operations' and the other one 'Control'
In Control sheet I have a data base consisting of several columns with headers such as: Date, Document Number, Name, Model, Quantity, Unit Price, Total Price

This sheet is intended to have a list of different items, so, let's say I filled up two rows with the next information (Corresponding to the order I already gave) 

It should look pretty much like this:

Header:       Date,         Document Number,    Name,        Model,       Quantity,       EA,     Total Price

Row 2:   July 7th 2013,      A0325,                   Pisa,     QTX-2302,        3,          $20.00,       $60.00

Row 3:   July 7th 2013,      A0325,                   Pisa,     PEK-6969,        7,          $15.00,      $105.00

Row 4:   July 8th 2013,      A0467,                  Beca,     QTX-2302,        2,          $18.00,       $36.00

Now in the 'Operations' sheet (Row 1 with headers) I'd like to do the next:

Objective 1:

In B32 cell, I'm writing 'A0325' 

In let's say H32 (Always 'B' same row) cell, I want to make a Sum of 'Total Price' column in 'Control' sheet,  only if, once found the text I wrote in B32, in Column 2 (Document number) in the 'Control' sheet. Then every item in 'Control' sheet with the reference 'A0325' will be included in that Sum. In this example  it should make a sum of the values $60.00 and $105.00

Objective 2:

In B32 cell, I'm writing 'A0325' 

In let's say F32 (Always 'B' same row) cell, I want a formula that once that search and finds the reference 'A0325' I wrote in B32 cell, brings the text written under column 'Name' in the row where the reference was found.

This may be a little pain, even when the 'Document Number' text value (A0325) will always have the same text value under 'Name' column ('Pisa' in this example), in other words: A 'Document Number' will never have two or more different values in 'Name' column. But I wonder what will happen when two or more same 'Name' values will be found in the 'Control' sheet.

'Operations' sheet should look like this (Not necessarily in this column order) :

Column:         A                      B                          F                           H

Header:       Date,         Document Number,       Name,                  Total Sum

Row 32:   July 7th 2013,      A0325,                   Pisa,                     $165.00

Row 33:   July 8th 2013,      A0467,                   Beca,                      $36.00

Well, I think this is so far what I would like to have so far.

If you want me to share you my Spread Sheet document fell free to ask for it.

One more time I thank you so much for your invaluable given support my most fine samaritan friend.

Hekman

Solution:

Have a look at the screenshot of sheet 'Control':

Now, put the following formula in your sheet 'Operations':

=query('Control'!A:G;"select max(A),B,C,sum(G) where B<>'' group by B,C label max(A) 'Date', sum(G) 'Total Price' ";1)


Or if you don't want Row headers then try the following formula:

=query('Control'!A:G;"select max(A),B,C,sum(G) where B<>'' group by B,C label max(A) '',B '',C '',sum(G) '' ";1)


Have a look at the screenshot of sheet 'Operations':

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive