How to use dynamic array formula to filter and format data-CodePudding

I am facing a problem related to the dynamic array.

I have data in the below format.

And I want to convert to this format.

The formula opens with { to open an

So basically, I organize the data with arrays of the same amounts of columns. The first one with part

= {   => To open the array.

"Fruits:","";  => This create a cell with "Fruits:"   an empty cell.

QUERY(B5:D,"select C, D where B ='Fruits'");  => which is 
already on an array of 2 columns.

{"Total:",SUMIF(B5:D,"Fruits",D5:D)}; => Creates the "Total" cell   the sum 
of values that has Fruits in column B.

"","";  => Which will create an empty row to separate the information
for the next set of arrays.

You do the same pattern for the other categories.

}  => to end the initial array.

You can add a "

Reference:

CodePudding user response：

To build the result table without hard coding category names in the formula, use the recently introduced lambda functions, like this:

={ 
  lambda( 
    data, categories, headers, totalsHeader, blankRow, selectPrice, 
    reduce( 
      headers, query(unique(categories), "where Col1 is not null", 0), 
      lambda( 
        resultTable, filterKey, 
        { 
          resultTable; 
          lambda( 
            filterData, 
            { 
              filterData; 
              { totalsHeader, query(filterData, selectPrice, 0) }; 
              blankRow 
            } 
          )(filter(data, categories = filterKey)) 
        } 
      ) 
    ) 
  )( 
    B5:D, 
    B5:B, 
    B4:D4, 
    { "", "Total:" }, 
    { "", "", "" }, 
    "select sum(Col3) label sum(Col3) '' " 
  ); 
  { "", "Grand Total:", sum(D5:D) } 
}

See { array expressions }, filter(), query(), reduce() and lambda().

The formula will repeat each category name on several rows. If they get in the way, you can hide them from view by using a conditional formatting custom formula rule.

CodePudding user response：

I suggest you read on: https://stackoverflow.com/a/58042211/5632629

the first part of your formula outputs a grid of 4×3 cells

the second part of your formula outputs a single cell

if you want to combine it properly use:

={FILTER(A5:D11, B5:B11="Fruits");
 {"","","Totals",SUM(FILTER(D5:D11, B5:B11="Fruits"))}}

or:

={FILTER(B5:D11, B5:B11="Fruits");
 {"","Totals",SUM(FILTER(D5:D11, B5:B11="Fruits"))}}