How to use Arrayformula on some of the rows in two ranges-CodePudding

I'm trying to make some protected sheet which will combine data from two others:

Sheet1 - Has mix of entries:

Sheet2 - Has only t1 entries

Sheet3 (protected) - Need ARRAYFORMULA for this sheet to be able to make following:

So in Sheet3 I need to take all t1 entries from Sheet1 and all entries from Sheet2 and list them in Sheet3 ordered by Date.

But so far I could do only ARRAYFORMULA(Sheet1!A2:C5) which copies all entries from Sheet1

CodePudding user response：

This is a job for QUERY formula: Try this in Sheet3:

=query({Sheet1!A1:C;Sheet2!A1:C},"select * where Col1='t1' order by Col3 asc",1)

Find working example here: https://docs.google.com/spreadsheets/d/1P6uYZcQJqKt7OVPhYtMoDkssfFbhsJVSGACI8u9qMmo/copy

First: I combine 2 ranges using { } notation {range1;range2} means that I stack 2 ranges - one on another. Then using QUERY i select all the Columns in my combined range and order them by third column. Number 1 at the end of the formula means that range has one row of headers.

CodePudding user response：

=sort({filter(Sheet1!A2:C5,Sheet1!A2:A5="t1");filter(Sheet2!A2:C5,Sheet2!A2:A5="t1")},3,true)

works.

For larger sheets, you should be able to drop the numeral 5 in the above formula.

The {} bracket means local array. ; means vertical concatenation. For sort() and filter(), see official documentation.