The hard way and the easy way
This is a scenario that most of us have faced at some point. Our data is spread across multiple spreadsheets and we need to consolidate them into one sheet. Sometimes, we also want to "lookup" and add new columns from other sheets to not just combine, but also map, the data. I will handle the data mapping in another blog post—for now, let's just combine data coming from multiple sources.
To start off, let's assume that you have the same number of columns to import and they contain the same data. E.g., sales by reps from different locations. You're combining the same data (sales in $$'s, sales rep's name, etc.) across these sheets.
The hard way: a two-step process.
Step 1: Importing data from multiple sheets into different tabs on a master spreadsheet
First, we are going to bring the data in from multiple different sheets into different tables in the same spreadsheet. To do this, first create a new sheet. Then add a new tab in that sheet to pull data in from your first source: let's call this tab 'Partial Data 1'.
We will use the IMPORTRANGE function to fill this sheet with data. This is how the function works:
Go to cell A1 of your 'Partial Data 1' sheet and add the formula above. Replace the stock hyperlink with a link to the GSheet that you wish to import from. Then replace the column indicator to highlight the columns you wish to import.
Hit enter after you type in the formula above. You should now see a #REF error. Hover over the cell to see this option asking you to provide access.
Click on the Allow access button and sit back as GSheets updates your new sheet with the data! Simply repeat this process as many times as you need with new tabs on your MASTER file.
Step 2: Consolidating the data from multiple tabs into one master tab
Great, now that you have all the data into your master spreadsheet, you still need to combine them into one Master tab. For this, we will use an array formulas combined with a query formula. Not ideal, I know.
To start off, let's copy and paste all the headers into a Master tab. We won't be pulling the headers from the Partial Data 1, 2, 3... tabs you've created on Step 1. On the new Master tab, type this in:
This will pull in all the data in the columns you've highlighted from all your partial data sets. However—here's the kicker—it will also download all the empty cells. ?
This means you will see your data from Partial Data 1, then a lot of empty cells (usually 1000 rows), then your data from Partial Data 2 and so on.
To remove these blanks, we will use the Query function. Let's start with wrapping a query function around the previous formula.
Next, we will write a short query to only include rows which aren't blank. Like this formula below.
Great, this should combine all the data you need into your Master tab.
The good folks at Learn Google Spreadsheets have created a video to walk through the process, in case it's easier to follow-on.
The easy way: with Airboxr
If you already have Airboxr installed, just open a new blank sheet (this will be your Master spreadsheet) and launch Airboxr by going to Add-ons → Airboxr. The click on Import.
On the next screen, copy and paste links to all your sales sheets.
After you have added your sources, you can use the same Import flow to import all (or some) of the data into your master sheet. You can even filter for multiple criteria when pulling the data—e.g., identify the sales people who have done more than $X from region Y. Here is how the import works.
Do the import as many times as you want to create your master sheet. Note that the headers get imported with every import, so you may need to remove them manually after you've completed the imports.
That's quick, isn't it? ⚡️
Give it a shot yourself. If you haven't already downloaded the add-on—do it now. The add-on is free until December 31st, 2020. And if you sign up now, we will hook you up with a sweet discount when we go paid.