Google-sheets – Combing two ranges with identical column structure from two different sheets (in same spreadsheet)

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-querysorting

I'm doing a simple ticketing system, where I have two sheets, "Form Responses" and "Email Responses" both of which are populated automatically as their names imply (one from a form, one from an email by using Zapier). Both sheets have the same header columns (e.g.: Timestamp, Email Address, Subject, Description).

I want to create a third sheet that has the same headers again but has content from both "Form Responses" and "Email Responses", sorted by Timestamp.

I tried ={'Form Responses 1'!A2:H18,'Email Responses'!A2:H18} but it puts the sheets next to each other.

Best Answer

  • you need to separate them with ;

    =SORT({'Form Responses 1'!A2:H18; 'Email Responses'!A2:H18}, 1, 1)

  • if you want to keep one header try:

    =SORT({'Form Responses 1'!A1:H18; 'Email Responses'!A2:H18}, 1, 1)

  • if you want to go full dynamic try:

    =SORT({FILTER('Form Responses 1'!A1:H, 'Form Responses 1'!A1:H<>""); 
       FILTER('Email Responses'!A2:H, 'Email Responses'!A2:H<>""), 1, 1)}

  • you can also use a query like:

    ={'Form Responses 1'!A1:H1; 
 QUERY({'Form Responses 1'!A2:H18; 'Email Responses'!A2:H18}, 
 "select * where Col1 is not null order by Col1", 0)}

Related Topic