Automate the Data Enrichment Process in Excel using VBA

The goal is to automate the data enrichment process in Excel using VBA, which might involve adding extra information (like postal codes and cities) to an existing dataset based on certain criteria.

Objective:

Let’s assume you have a worksheet with customer data, including columns like « First Name, » « Last Name, » « Email, » « Country, » and you want to add more information (for example, « Postal Code » and « City ») from an external source or dataset.

VBA Code Structure:

1. Import data (e.g., from an external file or API).
2. Clean the data (e.g., remove duplicates, check for errors).
3. Enrich the data (e.g., add external information like postal codes or cities based on the country).
4. Export the results or update the worksheet.

Detailed VBA Code:

Sub EnrichData()
    ' Declare variables
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim LastRow As Long
    Dim i As Long
    Dim Country As String
    Dim PostalCode As String
    Dim City As String   
    ' Set worksheets
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Source sheet with existing data
    Set wsDestination = ThisWorkbook.Sheets("Sheet2") ' Destination sheet where enriched data will go
    ' Find the last row with data in column A of the source sheet
    LastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    ' Loop through each row of data
    For i = 2 To LastRow ' Start from row 2 to skip headers
        ' Get the country value from the current row
        Country = wsSource.Cells(i, 4).Value ' Assume the "Country" is in column 4       
        ' Enrich the data based on the country (simple examples)
        If Country <> "" Then
            ' Enrichment logic: Adding a postal code and city based on the country
            Select Case Country
                Case "France"
                    PostalCode = "75000"
                    City = "Paris"
                Case "Belgium"
                    PostalCode = "1000"
                    City = "Brussels"
                Case "Germany"
                    PostalCode = "10115"
                    City = "Berlin"
                Case Else
                    PostalCode = "N/A"
                    City = "Unknown"
            End Select           
            ' Write the enriched data to the destination sheet
            wsDestination.Cells(i, 5).Value = PostalCode ' Add Postal Code to column 5
            wsDestination.Cells(i, 6).Value = City ' Add City to column 6
        End If
    Next i
    MsgBox "Data enrichment completed successfully!"
End Sub

Code Explanation:

1. Variable Declaration:
   • wsSource and wsDestination are variables referencing the source and destination worksheets.
   • LastRow is used to find the last row with data in column « A » of the source sheet.
   • Country, PostalCode, and City are variables used to temporarily store data for enrichment.
2. Set Worksheets:
   • wsSource is the worksheet containing the original data (e.g., customer details).
   • wsDestination is the worksheet where the enriched data (e.g., postal code and city) will be placed.
3. Find the Last Row:
   • LastRow is determined by looking for the last filled cell in column A (assuming column A contains customer data like « Name » or « Email »).
4. Loop to Process Each Row:
   • A For loop processes each row in the source sheet, starting from row 2 (to skip headers).
   • For each row, the value of the « Country » column (column 4) is retrieved.
5. Enrich the Data:
   • A Select Case structure checks the value of Country. Based on the country, it assigns a postal code and city (this is a simple example).
   • If a valid country is found, the corresponding postal code and city are written into the destination sheet (columns 5 and 6).
6. Completion Message:
   • After the loop is finished, a message box (MsgBox) pops up to notify that the data enrichment is complete.

Possible Enhancements:

• Importing Data from an API: You could enrich the data by pulling external information (e.g., via an API or a database). This would involve using XMLHttpRequest to query an API and retrieve data in JSON format.
• Data Validation and Cleaning: Before enriching the data, you can add steps to clean it, such as checking for missing or invalid country names.
• Enrichment from External Files: Instead of using hardcoded values, you could pull the postal code and city data from an external file (like another Excel file or a database) to enrich the data.

Conclusion:

This code provides a basic structure to automate the data enrichment process in Excel via VBA. It can be customized based on specific needs, such as integrating with external systems, handling more complex data, or performing additional data cleaning tasks.