troubleshooting Question

Excel - Identifying most common series of date values

Avatar of alisonthom
alisonthom asked on
Microsoft Excel
5 Comments1 Solution468 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Angelp1ay

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros