Combine Multiple Group of columns from 1 sheet to 1 master data

Question

I'm trying to combine data I createed for my workers to 1 master sheet, I created one sheet for all the workers so they can check or backup to other if the job not yet done.

I'm trying to use a query or importrange but I'm not the getting desired results.

Here is the example of data in google sheet, hope someone can help.

Thanks a lot

Google Sheet: https://docs.google.com/spreadsheets/d/1snx8EbFR_YhDflqR5rYWdDrN3ORSi7XAo5Ex3bQxPzE/edit?usp=sharing

RemcoE33 RemcoE33 · Accepted Answer · 2020-12-24T13:17:42

I created a solution inside your sheet. We combine:

ARRAYFORMULA - To stack them under each other using { }
QUERY - To filter out the blank rows
IMPORTRANGE - to get the data

SOLUTION - in the same spreadsheet

=ARRAYFORMULA({
  QUERY({Data1!A1:E},"SELECT * WHERE Col1 IS NOT NULL",1);
   QUERY({Data2!A1:E},"SELECT * WHERE Col1 IS NOT NULL",1)
     })

SOLUTION - with importrange from a other spreadsheet. (same a above but get data via importrange)

=ARRAYFORMULA({
  QUERY(IMPORTRANGE("1snx8EbFR_YhDflqR5rYWdDrN3ORSi7XAo5Ex3bQxPzE","Data1!A1:E"),"SELECT * WHERE Col1 IS NOT NULL",1);
    QUERY(IMPORTRANGE("1snx8EbFR_YhDflqR5rYWdDrN3ORSi7XAo5Ex3bQxPzE","Data2!A1:E"),"SELECT * WHERE Col1 IS NOT NULL",1)
       })

I hope this what you are after.

Combine Multiple Group of columns from 1 sheet to 1 master data

2 Answers