CODEWITHSUNDEEP
google-sheets
Cory
Cory

Reputation: 1283

Missing second array data using importrange in google sheets

I am trying to join two columns of data from two separate sheets. The first range appears fine, but the second briefly shows "loading" but is over written by the fist range of data. I have attached an example. Here is the formula: =ARRAYFORMULA({Sheet2!A1:A; Sheet3!A1:A})

In this example, the data from Sheet3 is not appearing. My assumption is that it is because the range is dynamic (I don't know how much data will be in each column).

Upvotes: 0

Views: 222

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5529

The reason is because you need to sort your arrays OR filter out the blanks - it basically tends to only include the second array below the rest of the rows of the first one, including blank rows

if order of the data DOESN'T matter to you, you can simply wrap the entire function with a final SORT.

=sort(ARRAYFORMULA({Sheet2!A1:A; Sheet3!A1:A}))

enter image description here

If order DOES matter, you can use this function:

={FILTER(Sheet2!A1:A,LEN(Sheet2!A1:A)>1);FILTER(Sheet3!A1:A,LEN(Sheet3!A1:A)>1)}

enter image description here

Upvotes: 1

Cory
Cory

Reputation: 1283

I was able to get it working with the following: =QUERY({Sheet2!A1:A; Sheet3!A1:A}, "where Col1 is not null and Col1 <> ''")

filtering out the blanks prevented it from overwriting the data from the second range, I presume

Upvotes: 0

Related Questions