How can I rearrange the output of a query based on the order of another sheet?-CodePudding

I have the following query I'm working on/building for my work (located in the 'Query Result' sheet of my example link).

=QUERY({'1st'!A7:D;'2nd'!A7:D}, "SELECT Col1, SUM(Col2), SUM(Col3), SUM(Col4) GROUP BY Col1")

It's doing the calculation just as I intended, but I need it in the sort order shown in the "Master Chart" sheet. I've tried to use ORDER BY, but since I'm using GROUP BY with aggregation functions, it's not functioning how I expect.

Can someone show me the best way to get the same result that the query is giving but in my desired order?

A simplified copy of my sheet is located HERE.

CodePudding user response：

Answer

The following formula should produce the result you desire:

=ARRAY_CONSTRAIN(SORT({QUERY({'1st'!A7:D;'2nd'!A7:D}, "SELECT Col1, SUM(Col2), SUM(Col3), SUM(Col4) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL SUM(Col2) '', SUM(Col3) '', SUM(Col4) ''"),SORT(FILTER(VLOOKUP(UNIQUE({'1st'!A7:A;'2nd'!A7:A}), {'Master Chart'!A2:A,'Master Chart'!E2:E},{1,2}, FALSE), UNIQUE({'1st'!A7:A;'2nd'!A7:A})<>""),1,TRUE)},6,TRUE),COUNTA(UNIQUE({'1st'!A7:A;'2nd'!A7:A})),4)

Explanation

This query is composed of two main parts, wrapped in =ARRAY_CONSTRAIN(SORT(...)).

Part A is QUERY(...) which does much the same thing as you already had, although I took the liberty of not including results where Col1 (tire size) is null, and I also removed automatically generated "sum" labels that =QUERY creates.

Part B is SORT(FILTER(VLOOKUP(...))), which sorts each row in Master Chart by the same order that the QUERY does. This then provides a Sort Order value (from Master Sheet) for each row returned by the QUERY.

The outermost SORT takes the results from Part A and sorts them using the Sort Order from Part B. Finally, the =ARRAYCONSTRAIN gets rid of the excess—Part B is only used for sorting and should not be shown in the final result.

If you wish to have the "sum" labels, you could add them back manually by enclosing the entire formula in the following:

={{"","sum","sum","sum"}; FORMULA HERE }

If you wish to count null results from Col1, this method will not work as you have not provided a distinct Sort Order for null results.

Functions used:

CodePudding user response：

Use vlookup() to only show the rows in the results of query() where the size matches the list in Master Chart, in the same order, like this:

=arrayformula( 
  iferror( 
    vlookup( 
      'Master Chart'!A2:A, 
      query( 
        { '1st'!A7:D;'2nd'!A7:D }, 
        "select Col1, sum(Col2), sum(Col3), sum(Col4) 
         group by Col1", 
        0 
      ), 
      { 1, 2, 3, 4 }, 
      false 
    ) 
  ) 
)