Combining data from multiple Excel files while removing duplicate records can be a useful task when working with large datasets. In this article, we will guide you through the process of merging three Excel files that contain people records, using a unique ID field as the primary key. By implementing VBA code, we will demonstrate how to consolidate the data into a single file, ensuring that no duplicate records are included.
Step 1: Set Up the Excel Files (80 words): Begin by opening the three Excel files that you want to merge. Each file should contain a sheet with people records, including a unique ID field. Make sure that the ID field exists in all three files and contains consistent data. It is crucial to organize the data in a similar structure across all files to ensure accurate merging and avoid data loss.
Step 2: Create a New Workbook and Insert VBA Code (100 words): In Excel, create a new workbook that will serve as the destination for the merged data. Press “Alt+F11” to open the Visual Basic Editor. Insert a new module by going to “Insert” > “Module.” This is where we will write the VBA code for combining the Excel files.
Step 3: Write the VBA Code to Merge the Data (250 words): Below is an example VBA code that demonstrates how to unite three Excel files based on the ID field and remove duplicates:
Sub MergeExcelFiles()
Dim masterWorkbook As Workbook
Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim lastRow As Long
Dim i As Long
‘ Set the master workbook as the current workbook
Set masterWorkbook = ThisWorkbook
‘ Loop through each source file
For i = 1 To 3 ‘ Adjust the loop range based on the number of source files
‘ Open the source workbook
Set sourceWorkbook = Workbooks.Open(“C:\Path\to\SourceFile” & i & “.xlsx”)
‘ Set the source sheet
Set sourceSheet = sourceWorkbook.Sheets(“Sheet1”)
‘ Find the last row in the master workbook
lastRow = masterWorkbook.Sheets(“Sheet1”).Cells(Rows.Count, “A”).End(xlUp).Row
‘ Copy the data from the source sheet, excluding the header row
sourceSheet.Range(“A2:C” & sourceSheet.Cells(Rows.Count, “A”).End(xlUp).Row).Copy _
masterWorkbook.Sheets(“Sheet1”).Range(“A” & lastRow + 1)
‘ Close the source workbook
sourceWorkbook.Close SaveChanges:=False
Next i
‘ Remove duplicate records based on the ID field
masterWorkbook.Sheets(“Sheet1”).Range(“A:C”).RemoveDuplicates Columns:=1, Header:=xlYes
End Sub
Conclusion: By following the steps outlined in this article and using the provided VBA code, you can unite three Excel files containing people records into a single workbook while eliminating duplicate entries based on the unique ID field. This process allows you to consolidate data efficiently and avoid duplication issues. Customize the code to match your specific file paths, sheet names, and ranges, ensuring a seamless merging process.