Sunday, April 8, 2012

yogi_Extract Unique Entity Rows Based On Specified Criterion

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #499    Apr 9, 2012

user digitaltoast said: 
Problems with query to automatically make unique rows. Tried pivot and group. An example of my problem and the explanation below is available here: 
And before anyone says "use sort and filter", a large part of this is to try and avoid that. I literally want it to be as simple as "paste data in sheet 1, sheet 2 is then ready to export as CSV". 
The problem: 
I'd like to "boil down" multiple rows for one product, into one product with multiple attributes. 
I've managed to populate column D with a representation of all sizes for that costume in the first row of that costume, using =ARRAYFORMULA(IF(AND(LEFT(A2,5)=LEFT(A3,5),LEFT(A2,5)=LEFT(A4,5)),CONCATENATE(MID(A2,6,2),",",MID(A3,6,2),",",MID(A4,6,2)),CONCATENATE(MID(A2,6,2),",",MID(A3,6,2)))) 
So now I need to group by column C which is column A stripped of its unique size. I tried =QUERY(A1:C10,"select A,B group by C",1) error: Invalid query: Cannot use GROUP BY when no aggregations are defined in SELECT. 
I've also tried =QUERY(A1:C10,"select A,B group by C",1) error: Invalid query: Cannot use PIVOT when no aggregations are defined in SELECT. 
I know my SELECT query works above as I've managed to select by "Sailor" and populate E,F. The problem is, I don't WANT to aggregate the selections. I've been at this most of the day, I'd really appreciate some help. 
Another less important question: How do I populate a table ONLY for the rows which meet the criteria, for example, where the product contains M? 
My query is: ARRAYFORMULA(QUERY($A:$C,"select A,B where 'MID(A:A,6,2)' contains 'M'")) 
But this fills the table with everything. BTW, I've tried just about everything with and without ARRAYFORMULA. 
following is a solution to the problem