Combining multiple excel worksheets into a master

Question

Hi,

I'm a newbie to excel. Can someone please help me. I have multiple worksheets containing the same headings. The actual data can be different. For example:

Worksheet: TST-11-111-111

Worksheet: TST-11-111-222

What i'd like the Master to do is have one line to appear for duplicate ID and a total in the quantity column.

Answer 1

Like anything else in Excel there is more than one way to do that. I am in the process of learning about one specific method, PowerQuery.  The advantage of PowerQuery over the others is the massive amount of built in automation. You can use PQ to import the data, "clean" it, manipulate it in various ways and PQ "remembers" everything you do. Then you can use that "query" to import more data and it will re-apply all of the steps you did previously.  Apparently PQ supports something like 80 different data sources

Sheets- - Consolidate data in multiple worksheetshttps://support.office.com/en-us/article/consolidate-data-in-multiple-worksheets-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b

To summarize and report results from separate worksheets, you can consolidate data from each into a master worksheet. The worksheets can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data, you are assembling data so that you can more easily update and aggregate as needed. For example, if you have an expense worksheet for each of your regional offices, you might use consolidation to roll these figures into a master corporate expense worksheet. This master worksheet might also contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.

Sheets- Combine All Sheets in a Workbook with Power Query                  2019 09 01          Matt Allington

https://exceleratorbi.com.au/combine-all-sheets-in-a-workbook-with-power-query/

You have a workbook with multiple sheets within a single workbook that need to be combined.

There are a couple of tricky issues to overcome with this pattern.

    Write the query in such a way that new sheets are automatically loaded

    Handle the errors with repeating header rows coming from the multiple sheets

    Handle the renaming of the Sheet Name column so it works regardless if the sheet order changes.

---

There are quite a few different common business scenarios where you get data in the form of a file on a regular basis.  The 3 common patterns I use most often are.

   1.         Each day/month/year you get a new file containing incremental data.  All the data in all the files need to be combined and loaded. I have previously shared this article explaining how to combine all files in a folder with Power Query.

   2.         Each day/month/year you get a new file and it replaces the old file. I wrote about this pattern in Load the Latest Version of a File with Power Query.

   3.         You have a workbook with multiple sheets within a single workbook that need to be combined.  This could be in addition to pattern 1 or 2 above.  This is what today’s post is about.

ET PT PivotTables Power Query BI.docx

sheets-**Combine Data on Two Worksheets**2015 01 22|Have you been experimenting with the Power BI tools that are available in the newer versions of Excel. I’ve done some work with PowerPivot, and was impressed by what could be done with…

Sheets- Combine Excel worksheets of the same name into one spreadsheet2013 09 29This article describes 3 possible ways to merge the same name worksheets across multiple workbooks into a single Excel sheet, and explains how to choose the solution that best meets your needs.

************************

Here are some more generic articles on PQ

Import data from external data sources (Power Query)https://support.office.com/en-us/article/import-data-from-external-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a

Use Excel's Get & Transform (Power Query) experience to import data into Excel from a wide variety of data sources. You can then use the Query Editor to edit query steps to shape or transform data. For more information, see Shape data. ET GT – Get & Transform - PowerQuery

@ Import and analyze datahttps://support.office.com/en-us/article/Import-and-analyze-data-ccd3c4a6-272f-4c97-afbb-d3f27407fcde#ID0EAABAAA=Import_data

You can use Excel’s Get & Transform (Power Query) experience to connect to multiple data sources, then use the Query Editor to shape and transform data.

    Getting Started with Get and Transform                          Import data from external data sources

    View and Manage Queries in a Workbook                      Add a query to an Excel worksheet

    Introduction to the Query Editor                                       Shape data

    Edit Query Step Settings

Import Data Into Your Excel Spreadsheets the Neat & Easy Way

Have you ever struggled with importing or exporting data into a spreadsheet? This tutorial will help you master the art of moving data between Microsoft Excel, CSV, HTML, and other file formats.

Consolidate in Excel with the CONSOLIDATE Tool

https://www.myexcelonline.com/blog/consolidate-in-excel-with-the-consolidate-tool/

The Consolidate tool in Excel is located in the Data menu and combines values from multiple ranges into one new range.

You would use this feature when you have a single text column on the left and the column has many duplicate values.

First you need to select a blank cell outside your data set and then go to Data > Consolidate.  You will then need to select all your data and make sure that the Top Row and Left column boxes are checked.

DOWNLOAD WORKBOOK

https://www.myexcelonline.com/wp-content/uploads/2015/07/Consolidate.gif

**Importing Data into Excel**            2011 06 08

Importing data to Excel from other programs and sources enables you to further analyse and generate reports or dashboards from what is often 1000’s of rows of data. On the Data tab of the ribbon in the section Get External Data there are four options:

.  *  From Access  – a Microsoft Access Database

.  *  From Web – from a web page

.  *  From Text – from a .csv or .txt file

.  *  From Other Sources – like an SQL, XML or other database

Answer 2

Hi Basma,

Welcome to this forum and we are glad to help you.

Here is my understanding of your scenario: You want to build one master worksheet which you can get other worksheet's contents there. If so, you can follow the steps below to do that:

1. Move to the original workbook. 
2. Right-click the workbook you want to copy>Move or Copy.
3. Select the Master book under To book option. Select the location for the worksheet on the master workbook>Check Create a  copy>Click OK. 

 

After that, you can find a copy for your original workbook under the master workbook:

 

Please have a try and share with me your outcome. 

Regarding "have one line to appear for duplicate ID and a total in the quantity column." please share with me more details about that. 

Regards,

Neo

Answer 3

Hi Basma, 

Have you checked my reply above? Do you still need any help?

Regards,

Neo

Hi Neo,

I did thank-you. Unfortunately, it doesn't do what i need it to do. Maybe I explained it poorly. Let me try to explain in a more simplistic manner.

I have a spreadsheet that has 5 different worksheets. All worksheets have the same titles for each column. The worksheets are a listing

1. All worksheets list parts numbers (unique identifiers), revision numbers, part title, quantity etc)
2. I need a master copy to replicate the information from the 5 worksheets but the quantity fields (in the master) to be accumulative .
3. The master should only list unique part numbers (no duplicate lines)
4. If anything changes on the 5 worksheets (no matter what column) it should be reflected on the master.
5. this is the clincher...if someone changes the master, it should be reflected in the appropriate corresponding worksheet.

Answer 4

Hi Basma, 

Have you checked my reply above? Do you still need any help?

Regards,

Neo