Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to convert daily info into monthly info

Posted on 2011-03-10
5
Medium Priority
?
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 35100149
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
ID: 35100208
Thanks, I'll try this...
0
 

Author Comment

by:pwflexner
ID: 35100587
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:
Michael Fowler earned 500 total points
ID: 35102254
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
ID: 35102854
Perfect!!  Thanks so much.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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.

597 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