Skip to main content
add query-pivot
Source Link
doubleunary
  • 15.7k
  • 9
  • 23
  • 59

Since your ultimate goal is to make a chart, you may want to pivot the data so that each fruit is in its own column.

Assuming the data is in Sheet1!A2:B, choose Insert > New sheet and enter this array formula in cell A2 of the new sheet:

=query( 
  Sheet1!A2:C, 
  "select A, max(C) 
   where A is not null 
   group by A 
   pivot B", 
  0 
)

Then create a chart based on the data in the new sheet.

If you really need to expand the data so that every date and fruit has a value, and that value is zero when no value appears in the source data, insert yet another sheet and enter this array formula in cell A2 of the new sheet:

=arrayformula( query( split( flatten( unique(trim(Sheet1!A2:A)) & "µ" & transpose(unique(Sheet1!B2:B)) ), "µ" ), "where Col1 is not null and Col2 is not null", 0 ) )

...and this formula in cell C2:

=arrayformula( iferror( vlookup(A2:A & B2:B, { Sheet1!A2:A & Sheet1!B2:B, Sheet1!C2:C }, 2, false), if(Sheet1!A2:A, 0, iferror(1/0)) ) )

Assuming the data is in Sheet1!A2:B, choose Insert > New sheet and enter this array formula in cell A2 of the new sheet:

=arrayformula( query( split( flatten( unique(trim(Sheet1!A2:A)) & "µ" & transpose(unique(Sheet1!B2:B)) ), "µ" ), "where Col1 is not null and Col2 is not null", 0 ) )

...and this formula in cell C2:

=arrayformula( iferror( vlookup(A2:A & B2:B, { Sheet1!A2:A & Sheet1!B2:B, Sheet1!C2:C }, 2, false), if(Sheet1!A2:A, 0, iferror(1/0)) ) )

Since your ultimate goal is to make a chart, you may want to pivot the data so that each fruit is in its own column.

Assuming the data is in Sheet1!A2:B, choose Insert > New sheet and enter this array formula in cell A2 of the new sheet:

=query( 
  Sheet1!A2:C, 
  "select A, max(C) 
   where A is not null 
   group by A 
   pivot B", 
  0 
)

Then create a chart based on the data in the new sheet.

If you really need to expand the data so that every date and fruit has a value, and that value is zero when no value appears in the source data, insert yet another sheet and enter this array formula in cell A2 of the new sheet:

=arrayformula( query( split( flatten( unique(trim(Sheet1!A2:A)) & "µ" & transpose(unique(Sheet1!B2:B)) ), "µ" ), "where Col1 is not null and Col2 is not null", 0 ) )

...and this formula in cell C2:

=arrayformula( iferror( vlookup(A2:A & B2:B, { Sheet1!A2:A & Sheet1!B2:B, Sheet1!C2:C }, 2, false), if(Sheet1!A2:A, 0, iferror(1/0)) ) )

Source Link
doubleunary
  • 15.7k
  • 9
  • 23
  • 59

Assuming the data is in Sheet1!A2:B, choose Insert > New sheet and enter this array formula in cell A2 of the new sheet:

=arrayformula( query( split( flatten( unique(trim(Sheet1!A2:A)) & "µ" & transpose(unique(Sheet1!B2:B)) ), "µ" ), "where Col1 is not null and Col2 is not null", 0 ) )

...and this formula in cell C2:

=arrayformula( iferror( vlookup(A2:A & B2:B, { Sheet1!A2:A & Sheet1!B2:B, Sheet1!C2:C }, 2, false), if(Sheet1!A2:A, 0, iferror(1/0)) ) )