Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #499 Apr 9, 2012 www.energyefficientbuild.com
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: https://docs.google.com/spreadsheet/ccc?key=0AjnO7ZzoX7i7dHB5TTFJMW5IMEtrRHI4ZmZVUHliemc
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
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: https://docs.google.com/spreadsheet/ccc?key=0AjnO7ZzoX7i7dHB5TTFJMW5IMEtrRHI4ZmZVUHliemc
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
No comments:
Post a Comment