google-sheetsgoogle-sheets-formuladynamicquery

Need a Google Sheet solution to combine different time series with varying dates

I have Dataset of several time series. The dates on Column1 and Values on Column 2 of all the series but dates are varying. Need a Google Sheet solution to combine all the dates into one Column and Values of different series in different Columns

Sample Data set.

Sample Data set image

Expected Result Expected Result image

Solution

  • If your first tab is called 'data' then this formula should produce the expected result:

    =QUERY({{data!A1:B1,"Value"};{data!A2:B,transpose(split((REPT(data!B1&"|",rows(data!A2:A))),"|"))};{data!C2:D,transpose(split((REPT(data!D1&"|",rows(data!C2:C))),"|"))};{data!E2:F,transpose(split((REPT(data!F1&"|",rows(data!E2:E))),"|"))};{data!G2:H,transpose(split((REPT(data!H1&"|",rows(data!G2:G))),"|"))}},"select Col1,sum(Col2) where Col1 is not null group by Col1 pivot Col3")

    I created this example sheet to demonstrate:

    https://docs.google.com/spreadsheets/d/16_Xm-BVhE3AXvcr6dQe_OpgSUGYIOwt2R8brr-JS3xE/edit?usp=sharing