Okay, the code for this really isn't too advanced, but it is a little tedious. I designed this one for you that should accomplish what you need. It is filled with commentary you can use to help learn the code as well (in case you want to do this again on your own). Good luck!
Sub normalizeData()
Dim i As Integer, j As Integer, k As Integer
Dim r As Integer
Dim bReport As Workbook, Report As Worksheet, Report2 As Worksheet
Dim m(0 To 9) As Integer 'This sets up the variables for your milestones
Dim mileString As String, dateString As String
Set Report = Excel.ActiveSheet
Set bReport = Report.Parent
Set Report2 = bReport.Worksheets.Add
'There are two ways to identify your milestones to the subroutine: either have some kind of identifier to search _
for within the value of the milestone cell; or define them explicitly. I'm going to do the latter since I don't know _
what might be in those cells.
m(0) = 33 'The column number for each milestone
m(1) = 35
m(2) = 37
m(3) = 39
m(4) = 41
m(5) = 43
m(6) = 45
m(7) = 47
m(8) = 49
m(9) = 51
r = Report.UsedRange.Rows.Count 'Get the last row number and assign it to a _
short variable (just to make things easier).
Report2.Cells(1, 1).Value = "Normalized Data" 'Placing a header row allows us to use used range without having _
to worry about identifying the first iteration. If you've ever tried this _
by yourself you probably know what I'm referring to.
With Report2.Range("A1:D1") 'Just makin it purdy
.Merge
.HorizontalAlignment = xlCenter
.Interior.Color = RGB(0, 20, 99)
.Font.Color = RGB(224, 238, 255)
.Font.Bold = True
.Font.Size = 14
End With
For i = 2 To r
For j = 0 To 9
If Report.Cells(i, m(j)).Value <> "" And _
Report.Cells(i, m(j)).Value <> "Null" Then 'I'm not sure if the empty values will be null or blank strings.
mileString = Report.Cells(i, m(j)).Value
dateString = Report.Cells(i, m(j) + 1).Value
k = Report2.UsedRange.Rows.Count + 1
Report2.Cells(k, 1).Value = Report.Cells(i, 1).Value
Report2.Cells(k, 2).Value = Report.Cells(1, m(j)).Value 'Assuming your header is on the first row
Report2.Cells(k, 3).Value = mileString
Report2.Cells(k, 4).Value = dateString
Report2.Cells(k, 4).NumberFormat = "MM/dd/yyyy"
End If
Next j
Next i
For i = 2 To Report2.UsedRange.Rows.Count
If i Mod 2 = 0 Then
Report2.Range("A" & i & ":D" & i).Interior.Color = RGB(227, 235, 252) 'Alternating row color for easier reading.
End If
Next i
'************** This part adds borders. Omit this block if you don't want them.************
With Report2.Range("A1:D" & Report2.UsedRange.Rows.Count)
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlLeft).Weight = xlThin
.Borders(xlRight).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
End With
'******************************************************************************************
End Sub
PS: make sure you are on the denormalized worksheet when you run the macro. It is designed to reference that sheet as the active one and place the normalized data into a new worksheet.
Also note that I used an array to identify the milestones, but I'm sure you could just as easily use a For...Next loop with a Step 2 increment for improved scalability. For example, in this case, the beginning of your for loop would look like For j = 33 to 51 Step 2. This is assuming all your milestones are two columns apart.
