Sometimes you gain access to structured data but it isn’t a straightforward Copy and Transpose job to get the data into a useful format for analysis or database ingestion. To handle these situations you can use some Excel VBA to get what you need and even perform some useful transformations at the same time. In this example notice that the original cell contents are “Start Date: 1/1/2020 00:00:00” which Excel doesn’t understand to be a datetime object. Removing the “Start Date:” string from that cell while inserting just the numeric date time parts results in a cell that Excel understands as a date time value as opposed to a string of characters.
Objectives:
Get the relevant data into a structure ready for pivot table analysis, database ingestion, or Tableau analysis
Get the “Start Date” into a proper format that Excel understands
Only include FINISHED, SCHEDULED, RUNNING, or STOPPED states (DRAFT will not be included in results)
Excel Data (Before):
Excel VBA:
Dim i As Long
Dim nextB As Long
'the data pasted in has a header row so start at row 2
nextB = 2
'print header row
Cells(1, 3) = "Status"
Cells(1, 4) = "StartDate"
Cells(1, 5) = "CampaignName"
Cells(1, 6) = "Channel"
Application.ScreenUpdating = False
For i = 1 To Rows.Count
'exit the loop if 4 empty rows are seen
If Cells(i, 1).Value = "" And Cells(i + 1, 1).Value = "" And Cells(i + 2, 1).Value = "" And Cells(i + 3, 1).Value = "" Then
Exit For
'only load campaigns in FINISHED, SCHEDULED, RUNNING, or STOPPED states
ElseIf Cells(i, 1).Value = "FINISHED" Or Cells(i, 1).Value = "SCHEDULED" _
Or Cells(i, 1).Value = "RUNNING" Or Cells(i, 1).Value = "STOPPED" Then
'transpose contents of rows while iterating
Cells(nextB, 3) = Cells(i, 1)
'remove "Start Date" to transform string into date
Cells(nextB, 4) = Replace(Cells(i + 2, 1), "Start Date:", "")
Cells(nextB, 6) = Cells(i + 1, 1)
Cells(i - 1, 1).Select
'copy and paste preserves the original Hyperlink
Selection.Copy
Cells(nextB, 5).Select
ActiveSheet.Paste
nextB = nextB + 1
End If
Next i
Application.ScreenUpdating = True
End Sub
After running the Excel VBA macro:
