8020REI Logo

Overview

The goal of this solution is to automate data transformation in Excel for compatibility with LaunchControl, a digital marketing tool. Specifically, we're dealing with a dataset where multiple phone numbers and other data points are stored in one row per individual or entity.


LaunchControl, however, has a limitation where it only recognizes the first 3 phone numbers for a given property, while the rest get marked as "LandLine" and subsequently do not function as intended. To overcome this limitation, we have developed a VBA Macro that efficiently organizes and structures the data to be compatible with LaunchControl's requirements. 


Use this Excel file as a template.


This Macro does the following:

  1. It duplicates rows based on the "Action Plan" column, copying groups of three phone numbers to each new row.
  2. It ensures that each duplicated row is unique by appending a counter to the "Last Name" field.
  3. It removes rows that don't have a phone number in the "PHONE NUMBER1" column.
  4. The end result is a new sheet named "outcome" that is structured according to the rules of LaunchControl.


Where to Paste the Macro Code

Here is where you should paste the Macro code:


Windows:

  1. Open your Excel workbook.
  2. Press Alt + F11 to open the VBA editor.
  3. In the Project - VBAProject pane, right-click on your workbook and click Insert > Module. This will create a new module.
  4. Double-click the newly inserted module, this will open the module's code window.
  5. Paste the code into this module's code window.


MacOS:

  1. Open your Excel workbook.
  2. Click on Tools from the menu, then Macro, and finally Visual Basic Editor.
  3. In the Project - VBAProject pane, right-click on your workbook and click Insert > Module. This will create a new module.
  4. Double-click the newly inserted module, this will open the module's code window.
  5. Paste the code into this module's code window and then close the windows.

Macro Code and Brief Explanation

Here is the VBA Macro code you can copy and paste into your VBA editor:


* Make sure the Sheet name that you are working on is "Source".


Sub CopyData()

    Dim wsSource As Worksheet
    Dim wsOutput As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long, k As Long
    Dim sheetExists As Boolean
    Dim sht As Worksheet
    Dim nextOutputRow As Long
    Dim phoneSets As Long
    Dim dateStr As String
    Dim counter As Long
    Dim actionPlan As String
    Dim maxPhones As Long
    
    ' Check if output sheet exists, if yes then delete it
    sheetExists = False
    For Each sht In ThisWorkbook.Sheets
        If sht.Name = "output" Then
            sheetExists = True
            Application.DisplayAlerts = False
            sht.Delete
            Application.DisplayAlerts = True
            Exit For
        End If
    Next sht
    
    ' Set source worksheet
    Set wsSource = ThisWorkbook.Sheets("Source")
    
    ' Create new output worksheet
    Set wsOutput = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsOutput.Name = "output"

    ' Copy headers
    wsSource.Range("A1:BE1").Copy wsOutput.Range("A1")
    wsOutput.Range("BF1").Value = "PHONE NUMBER1"
    wsOutput.Range("BG1").Value = "PHONE TYPE1"
    wsOutput.Range("BH1").Value = "PHONE NUMBER2"
    wsOutput.Range("BI1").Value = "PHONE TYPE2"
    wsOutput.Range("BJ1").Value = "PHONE NUMBER3"
    wsOutput.Range("BK1").Value = "PHONE TYPE3"
    
    ' Get the last row of the source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' Initialize next output row
    nextOutputRow = 2
    
    ' Generate date string
    dateStr = "-" & Format(Date, "yyyy-mm-dd")
    
    ' Loop through each row in the source sheet
    For i = 2 To lastRow
        ' Get the action plan
        actionPlan = LCase(wsSource.Range("R" & i).Value)
        
        ' Determine how many sets of phone data we have based on action plan
        If InStr(actionPlan, "urgent") > 0 Then
            phoneSets = Application.WorksheetFunction.CountA(wsSource.Range("BF" & i & ":CE" & i)) / 2
        ElseIf InStr(actionPlan, "high") > 0 Then
            phoneSets = Application.WorksheetFunction.Min(6, Application.WorksheetFunction.CountA(wsSource.Range("BF" & i & ":CE" & i)) / 2)
        ElseIf InStr(actionPlan, "low") > 0 Then
            phoneSets = Application.WorksheetFunction.Min(3, Application.WorksheetFunction.CountA(wsSource.Range("BF" & i & ":CE" & i)) / 2)
        Else
            phoneSets = 0
        End If
        
        ' Skip if there are no phone sets
        If phoneSets = 0 Then GoTo NextIteration
        
        ' Initialize counter
        counter = 1
        
        ' Determine how many phones to copy per row
        maxPhones = IIf(phoneSets Mod 3 = 0, 3, phoneSets Mod 3)
        
        ' Copy over the data for each set of phone numbers
        For j = 1 To phoneSets Step 3
            ' Copy non-phone data
            wsSource.Range("A" & i & ":BE" & i).Copy wsOutput.Range("A" & nextOutputRow)
            
            ' Append the date string and counter to the owner last name
            wsOutput.Range("D" & nextOutputRow).Value = wsOutput.Range("D" & nextOutputRow).Value & "-(" & counter & ")-" & dateStr
            
            ' Copy phone data
            For k = 0 To 2
                wsSource.Range("BF" & i & ":BG" & i).Offset(0, (j + k - 1) * 2).Copy wsOutput.Range("BF" & nextOutputRow).Offset(0, k * 2)
            Next k
            
            ' Increment the next output row
            nextOutputRow = nextOutputRow + 1
            
            ' Increment counter
            counter = counter + 1
        Next j
        
NextIteration:
    Next i

End Sub

How it Works:

  1. Define Source and Outcome Worksheets: The Macro first identifies the source worksheet, where your original data is located. It then deletes any pre-existing "outcome" sheet and creates a new one.
  2. Copy Headers: It copies the column headers from the source to the outcome sheet.
  3. Iterate Through Each Row: The Macro starts a loop going through each row in the source sheet.
  4. Check 'Action Plan': For each row, it checks the value in the "Action Plan" column and sets a variable phoneColumns depending on this value ("Prospect Urgent", "Prospect High B", or "Prospect Low B"). This variable determines how many phone numbers will be copied for each row.
  5. Copy Data: It begins another loop to copy the values in columns A to T, and a group of three phone numbers (or fewer, depending on phoneColumns) to the outcome sheet. This process is repeated for each group of three phone numbers.
  6. Clean Up: At the end, the Macro loops through each row in the outcome sheet and deletes any rows that do not have a phone number in the "PHONE NUMBER1" column.

    This Macro will take approximately 5 minutes to run, depending on the size of your Excel file.


How to Use This Macro:

  1. Copy the Macro Code: Copy the provided code.
  2. Paste the Code: Open your Excel workbook, hit Alt + F11 to open the VBA editor. Then, insert a new module (Insert -> Module) and paste the copied code into this module.
  3. Run the Macro: In the VBA editor, hit F5 or select Run -> Run Sub/UserForm to execute the Macro.
  4. Remember to save your workbook as a Macro-Enabled Workbook (.xlsm) to ensure the Macro is saved and will be available for future use.