Excel is a powerful tool used extensively in various industries for data management. When dealing with multiple Excel sheets containing related data, consolidating them into a single sheet can save time and effort. In this article, we will explore how to consolidate multiple Excel sheets using Employee ID as a unique value.
Prerequisites
Before we begin, ensure the following:
- You have Microsoft Excel installed on your computer.
- The Excel sheets you want to consolidate have a common column containing unique Employee IDs.
Consolidating Excel Sheets
To consolidate multiple Excel sheets, follow these steps:
Step 1: Create a new sheet as a destination
Open a new Excel workbook and create a new sheet as a destination for consolidated data. Let's name it "ConsolidatedData."
Step 2: Link the source data
In the "ConsolidatedData" sheet, click on an empty cell where you want to display data from the first source sheet. Then, click on the "Formulas" tab in the ribbon, and select "From Other Sources" and "From Workbook."
In the "Link to Other Workbooks" dialog box, navigate to the location of the first source file, select the sheet, and click "OK."
Step 3: Consolidate data
In the "Formula" input box, type the following formula:
=CONSOLIDATE(REF(A1), "Sheet1!$A$1:$C$10")
Replace "Sheet1" with the name of the source sheet and "A1:C10" with the range of cells containing data you want to consolidate. Press Enter to apply the formula.
Repeat steps 2 and 3 for each source sheet.
Step 4: Consolidate formulas
If you have formulas in the source sheets that you want to consolidate, you need to consolidate those formulas as well. To do this, click on the cell containing the formula in the destination sheet, and then click on the "Formulas" tab in the ribbon. Select "Consolidate Function," and choose the function type and reference to the source cells.
Code Example
Here's an example of how to consolidate data using VBA:
Sub ConsolidateData()
Dim SourceFile1 As String
Dim SourceFile2 As String
Dim DestinationFile As String
Dim DestinationWB As Workbook
Dim SourceWB1 As Workbook
Dim SourceWB2 As Workbook
Dim SourceSheet1 As Worksheet
Dim SourceSheet2 As Worksheet
Dim DestinationSheet As Worksheet
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastCol As Long
Dim ConsolidatedRange As Range
SourceFile1 = "C:\Path\To\SourceFile1.xlsx"
SourceFile2 = "C:\Path\To\SourceFile2.xlsx"
DestinationFile = "C:\Path\To\DestinationFile.xlsx"
Set DestinationWB = Workbooks.Open(DestinationFile)
Set DestinationSheet = DestinationWB.Sheets("ConsolidatedData")
Set SourceWB1 = Workbooks.Open(SourceFile1)
Set SourceSheet1 = SourceWB1.Sheets("Sheet1")
LastRow1 = SourceSheet1.Cells(SourceSheet1.Rows.Count, "A").End(xlUp).Row
Set ConsolidatedRange = DestinationSheet.Range("A1:C" & LastRow1 + 1)
SourceWB1.Activate
ConsolidatedRange = Application.WorksheetFunction.Consolidate(SourceSheet1.Range("A1:C" & LastRow1), xlConsolidateSum)
SourceWB1.Close SaveChanges:=False
Set SourceWB2 = Workbooks.Open(SourceFile2)
Set SourceSheet2 = SourceWB2.Sheets("Sheet1")
LastRow2 = SourceSheet2.Cells(SourceSheet2.Rows.Count, "A").End(xlUp).Row
ConsolidatedRange.Offset(LastRow1, 0).Resize(LastRow2 - LastRow1 + 1, 3).Value = SourceSheet2.Range("A1:C" & LastRow2).Value
SourceWB2.Close SaveChanges:=False
DestinationWB.Save
End Sub
References