Solved

How to convert daily info into monthly info

Posted on 2011-03-10
5
377 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:Michael74
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:
Michael74 earned 125 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

763 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