Solved

Excel - Identifying most common series of date values

Posted on 2013-12-13
6
361 Views
Last Modified: 2013-12-14
Hi,

I have an Excel workbook (attached) that has 5 groups and each group has a series of date ranges.  The number of groups and number of date ranges are not fixed so another example could have say 15 groups and each group has 7 date ranges.

01/01/2013      31/12/2013
01/01/2014      31/12/2014
01/01/2015      31/12/2015
01/01/2016      31/12/2016
01/01/2017      31/12/2017
      
01/01/2005      31/12/2005
01/01/2006      30/09/2006
01/10/2006      31/12/2006
01/01/2007      31/12/2007
01/01/2008      31/12/2008
      
01/01/2005      19/04/2005
20/04/2005      31/12/2005
01/01/2006      31/12/2006
01/01/2007      19/04/2007
20/04/2007      19/04/2008
      
01/01/2005      31/12/2005
01/01/2006      31/12/2006
01/01/2007      31/12/2007
01/01/2008      31/12/2008
01/01/2009      31/12/2009

01/01/2005      31/12/2005
01/01/2006      31/12/2006
01/01/2007      31/12/2007
01/01/2008      31/12/2008
01/01/2009      31/12/2009

I need to determine the groups that have exactly the same series of date ranges.  In the attached workbook VBA was used to insert a formula in column C for the first date range in each group that concatenated the date start and end dates for each date range in that group.  The result is a long string of values.

The VBA then inserted a COUNTIF formula in column D to return the number of instances that the long string in column C occurred.  In the attached example the last 2 groups have exactly the same start and end dates.  Consequently, the values returned by the COUNTIF  formulae should be:     1,  1,  1, 2,  2

However, the values are:    1,  3,  1, 3,  3

I suspect the unexpected COUNTIF results arise from the very long strings involved.  I would really appreciate help with how I can get the results I am expecting in column D.

Many thanks in advance
Alison
Example.xls
0
Comment
Question by:alisonthom
  • 3
6 Comments
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 300 total points
ID: 39717994
I re-wrote your macro and it works fine :)
Sub MakeDateUids()
    ' Worksheet and ranges
    Dim sh As Worksheet
    Set sh = ActiveSheet
    
    Dim summaryCell, currentCell As Range
    Set summaryCell = Nothing
    
    ' Rows
    Dim row, firstRow, lastRow As Integer
    lastRow = sh.UsedRange.Rows.Count
    If sh.Cells(1, 1).Value <> "" Then
        firstRow = 1
    Else
        firstRow = sh.Cells(1, 1).End(xlDown).row
    End If
    
    ' Clear
    sh.Range("C:C").Clear
    
    ' Loop
    For row = firstRow To lastRow
        Set currentCell = sh.Cells(row, 1)
        If currentCell.Value <> "" Then
            If summaryCell Is Nothing Then Set summaryCell = currentCell.Offset(0, 2)
            summaryCell.Value = summaryCell.Value & CDbl(currentCell.Value) & CDbl(currentCell.Offset(0, 1).Value) & "/"
        Else
            Set summaryCell = Nothing
        End If
    Next
    
End Sub

Open in new window

DateGroups.xls
0
 
LVL 80

Expert Comment

by:byundt
ID: 39718254
Angelplay,
Unlike other languages (like VB.Net), in VBA you must declare each variable as something. Otherwise, it becomes a Variant.
Dim row, firstRow, lastRow As Integer         'row and firstRow are Variant, lastRow is Integer

Open in new window


Also, you should get in the habit of declaring integer variables as Long. Integer variables are padded with blank bits when stored in Windows apps, so they don't save any memory compared to Long variables. An Integer variable will cause a fatal overflow error if you exceed row 2^15-1 (32767)--something quite possible in a worksheet with 1048576 rows. The maximum value of a Long is 2^31-1 (2147483647)

Brad
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39718350
@byundt - Whoops. I normally put quite a lot of content into the comment but must have been falling asleep. Thanks for the tips on my 'ints', did not know either of those points.
0
 

Author Closing Comment

by:alisonthom
ID: 39718430
Hi Angelplay
thanks very much indeed for the quick and really helpful answer.  It works perfectly.
Alison
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39718597
You're most welcome Alison. Have a lovely day :)
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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 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

19 Experts available now in Live!

Get 1:1 Help Now