• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

How to convert daily info into monthly info

In column A are daily dates (weekdays only) from 1986 to 2010 in the format YYYYMMDDnot necessarily .  In column B is data corresponding to each day in column A.  I want to create in columns C and D a truncated version of columns A and B where column C only includes the first listed day of any given month (not necessarily the first of the month because the list only includes weekdays) and in column D the corresponding data from column B for that day.  For example:

Column A    Column B    Column C    Column D
20010330      aaa          20010403       ccc
20010331      bbb          20010501       fff
20010403      ccc            20060602      iii
20010404      ddd
     ...
20010430      eee
20010501      fff
20010502      ggg
     ...
20010530      hhh
20010602      iii
     ...
0
pwflexner
Asked:
pwflexner
  • 3
  • 2
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
This VBA will do what you are asking.

Note for this code to work all the date strings need to be in the format YYYYMMDD as the code needs to be able to extract the month and day values from the string for comparison.

Michael
Sub getfirstDays()
   Dim lastrow As Long, i As Long, month As Integer
   Dim d As Object, k As Variant
   
   Set d = CreateObject("Scripting.Dictionary")
   
   lastrow = Range("A" & Rows.Count).End(xlUp).Row
   
   For i = 2 To lastrow
      If Range("A" & i).Value <> "" Then
         month = Mid(Range("A" & i).Value, 5, 2)
         If Not d.exists(month) Then
            d.Add month, Array(Range("A" & i).Value, Range("B" & i).Value)
         ElseIf Right(d.Item(month)(0), 2) > Right(Range("A" & i).Value, 2) Then
            d.Item(month) = Array(Range("A" & i).Value, Range("B" & i).Value)
         End If
      End If
   Next
   
   i = 2
   For Each k In d.keys
      Range("C" & i).Value = d.Item(k)(0)
      Range("D" & i).Value = d.Item(k)(1)
      i = i + 1
   Next
   
End Sub

Open in new window

test.xls
0
 
pwflexnerAuthor Commented:
Thanks, I'll try this...
0
 
pwflexnerAuthor Commented:
Michael74,

When I run the macro I get 12 rows of data in columns C and D, beginning with the first new month in column A and including the next eleven months.  The years are not in sequence.  I'm attaching an Excel spreadsheet with the results.
getfirstday.xlsm
0
 
Michael FowlerSolutions ConsultantCommented:
Sorry about that

The attached code now allows for multiple years and sorts the data.

Michael
Sub getfirstDays()
   Dim lastrow As Long, i As Long, monthYear As Long
   Dim d As Object, k As Variant
   
   Set d = CreateObject("Scripting.Dictionary")
   
   lastrow = Range("A" & Rows.Count).End(xlUp).Row
   
   For i = 1 To lastrow
      If Range("A" & i).Value <> "" Then
         monthYear = Left(Range("A" & i).Value, 6)
         If Not d.exists(monthYear) Then
            d.Add monthYear, Array(Range("A" & i).Value, Range("B" & i).Value)
         ElseIf Right(d.Item(monthYear)(0), 2) > Right(Range("A" & i).Value, 2) Then
            d.Item(monthYear) = Array(Range("A" & i).Value, Range("B" & i).Value)
         End If
      End If
   Next
   
   i = 1
   For Each k In d.keys
      Range("C" & i).Value = Format(DateSerial(Left(d.Item(k)(0), 4), Mid(d.Item(k)(0), 5, 2), Right(d.Item(k)(0), 2)), "YYYYMMDD")
      Range("D" & i).Value = d.Item(k)(1)
      i = i + 1
   Next
   
   Range("C1:D" & i - 1).Sort key1:=Columns("C"), Order1:=xlAscending, Header:=xlNo
   
End Sub

Open in new window

getfirstday.xlsm
0
 
pwflexnerAuthor Commented:
Perfect!!  Thanks so much.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now