This the data I have -

A           B            C
12/15/2019  Apple       37
12/15/2019  Banana      11
12/15/2019  Carrot       1
12/15/2019  Damson       4
12/15/2019  Fig          1
12/16/2019  Apple       48
12/16/2019  Banana       6
12/16/2019  Carrot       3
12/16/2019  Eggplant     1
12/16/2019  Fig          5

It goes on for years. I have missing data points - See Damson and Eggplant don't exist for both dates.

I don't know if Google SpreadSheets can turn that data into a time line chart as is, of wether I'd need to transform into a table with all of A-F represented as columns on their own and missing entries set to zero explicitly.

I could probably write some Python to make a CSV representation as described, like that (even without the fancy libraries), but I wonder if it is possible in a s/sheet tech.

Is there a simple formula? What is the name for this transform?

1 Answer 1


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:

  "select A, max(C) 
   where A is not null 
   group by A 
   pivot B", 

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)) ) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.