asked on # Excel - Identifying most common series of date values

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

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

Microsoft Excel

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

@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.

Hi Angelplay

thanks very much indeed for the quick and really helpful answer. It works perfectly.

Alison

thanks very much indeed for the quick and really helpful answer. It works perfectly.

Alison

You're most welcome Alison. Have a lovely day :)

Unlike other languages (like VB.Net), in VBA you must declare each variable as something. Otherwise, it becomes a Variant.

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