Solved

How to convert daily info into monthly info

Posted on 2011-03-10
5
370 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
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

839 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