CODEWITHSUNDEEP
excelgoogle-sheets
Spencer
Spencer

Reputation: 455

Merge and sort data from multiple tabs to a single "Master" tab in Excel

Our company was using Google Sheets for the last several months and we had a system working great, but are now transitioning to Excel.

The tab in the Google Sheet where all the data is merged and sorted is called MASTER TEST and is found here

In Google Sheet we used formula:

=query({Data1, Data2, Data3},"Select Col1, ... where Col1 is not null order by Col1")

Data is merged: {Data1, Data2, Data3} is data from multiple tabs.

The result is merged and sorted data. How can I make the same report in Excel?

Basically, every call we receive is logged in a spreadsheet in a tab unique to each representative. We have a CURRENT WEEK tab which is supposed to hold a static version of all the calls received by every rep, sorted by date. In Sheets this was done with a =query() formula, but Excel does not seem to support such a thing.

I tried using Data>From Other Sources>From Microsoft Query but frankly this does not work since it only grabs the data from columns with data validation and gets very slow and breaks with too much data.

Upvotes: 0

Views: 1399

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

Pivot Tables in Excel VS query in Google Sheet

I suggest you using Pivot Tables, or Pivot Query in Excel. Here's some explanations:

  • Pivot Tables in Excel can handle big data very fast, query in Google Sheets can't do this
  • but query is more flexible and it refreshes simultaneously.

So if you are willing to work with data in excel, it may be harder to set your report. Sorting is easy task for Pivot Tables, but merging data from multiple tabs is not so easy.

Merging data

The goal in your case is to merge data from multiple tabs in excel. You can make it in two ways:

  1. Use VBA to collect data into single tab.
  2. Use Pivot Tables. Here's tutorial on how to accomplish this.

And still the best way for data manipulating is to use single tab for all your data.

Upvotes: 0

Related Questions