Excel Formula for Merging Multiple Files into a Master File

Objective

To merge multiple Excel files into a new master file while ensuring that:

• Matched headings are aligned in the same columns.
• Unmatched headings are added in new columns to the right.
• The data source is indicated by the respective file name.
• The headings are arranged as per a specified reference file (Sindh file).

Steps to Achieve the Objective

1. Prepare the Sindh File

This file will serve as the reference for headings. Ensure that:

• All required headings are included,
• They are in the desired order.

2. Use Power Query

The most effective way to achieve this merge is by using Power Query, which can be accessed in Excel.

Importing Data:

1. Open a New Excel Workbook: This will be your master file.
2. Go to Data Tab: Click on “Get Data.”
3. Select From File: Choose “From Folder” to load all files from the directory where they are stored.

Merging Files:

4. Choose Your Folder: Specify the folder that contains your Excel files.
5. Combine Files: In the navigator window, select “Combine” and choose “Combine & Load To.”
6. Adjust Queries: In the Power Query editor, follow these steps:
   • Promote Headers: Ensure headers are at the top.
   • Use Merge Queries: Use the ‘Merge Queries’ option to align matched headings and append unmatched headings.
   • Add File Name: You can extract file names using the SharePoint.Files function if working with SharePoint; otherwise, maintain a list within a separate query.

3. Organizing the Data

• Align Headings: After merging, ensure that the columns of matched headings align properly based on the Sindh file.
• Position Unmatched Columns: Add any unmatched headings to the right side.
• Color Coding: Use Conditional Formatting to highlight unmatched columns:
  • Go to "Home" > "Conditional Formatting" > "New Rule."
  • Set a rule to format cells based on whether they belong to unmatched headings.

4. Finalizing the Master File

• Ensure all data is properly aligned and formatted.
• Save the master file.

Excel Functions Used

While merging through the interface of Power Query, specific functions such as Table.PromoteHeaders, Table.Join, and Table.AddColumn get used in the background. However, if you choose to manipulate data directly in sheets, consider functions like:

• INDEX() for fetching data from specific rows/columns.
• MATCH() to find the position of a certain heading.
• IFERROR() to handle any errors gracefully as you structure your output.

Example

Assuming the following:

• Sindh File Headings: ID, Name, Address, Age
• Other Files Headings: ID, Name, Phone, Salary

The desired output format would be:

• Data from unmatched columns (Phone, Salary) appears on the right side of those in matched columns (ID, Name, Address, Age).

Conclusion

This approach allows for a systematic merging of multiple Excel files into a single master file with clear identification of the source data and proper heading alignment. For deeper learning about Power Query and data transformation techniques, consider exploring Enterprise DNA Platform courses.

By following the outlined steps, users can effectively manage their Excel data merging tasks with precision and clarity.