Solved

How to convert daily info into monthly info

Posted on 2011-03-10
5
346 Views
Last Modified: 2012-05-11
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
Comment
Question by:pwflexner
  • 3
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
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
 

Author Comment

by:pwflexner
Comment Utility
Thanks, I'll try this...
0
 

Author Comment

by:pwflexner
Comment Utility
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
 
LVL 23

Accepted Solution

by:
Michael74 earned 125 total points
Comment Utility
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
 

Author Comment

by:pwflexner
Comment Utility
Perfect!!  Thanks so much.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now