Search code examples
google-sheetspivottranspose

How can this sheet be reformatted to be more tabular?


Is there a way to take the data from this example and format it in the tabular structure below?

Google Sheets Example Google Sheets Link: https://docs.google.com/spreadsheets/d/1TsN5zDdZlZGVVSuzgwm-u8lQARKLP_0_QPrcMKrV1Ws/edit?usp=sharing

Priority ID Date Signups
No Priority 55001 10/13/20 1
No Priority 55001 10/14/20 6
No Priority 55001 10/15/20 3
Low Priority 55003 10/13/20 9
Low Priority 55001 10/13/20 4

Solution

  • use:

    ={"Priority", "ID", "Date", "Signups";
     ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(
     INDIRECT("Sheet1!C3:"& ROWS(Sheet1!C:C))="",,
     INDIRECT("Sheet1!A3:A"&ROWS(Sheet1!C:C))&"×"&
     INDIRECT("Sheet1!B3:B"&ROWS(Sheet1!C:C))&"×"&
     INDIRECT("Sheet1!A2:2")&"×"&
     INDIRECT("Sheet1!C3:"& ROWS(Sheet1!C:C)))), "×"), 
     "where Col2 is not null format Col3 'dd/mm/yyyy'", 0))}
    

    enter image description here