Can be interesting ...

murthy_v_s

Board Regular
Joined
Mar 2, 2007
Messages
83
Hi -

I am not sure if the following timeline is good enough to represent my question (as it reformats the text)

12/21|--------|12/29
01/01|-------------|02/02
01/20|----------------|02/16
02/26|----------|03/01
12/24 |---------------------------------|02/04

Let say I have three columns A (ID), B (start date), C(end date). In the above example, everything is represented for ID = 12345 (An ID can be repeated any times and input data is not sorted).
12345 12/21/2009 12/29/2009
12345 01/01/2010 02/02/2010
12345 01/20/2010 02/16/2010
12345 02/26/2010 03/01/2010
12345 12/24/2010 02/04/2010

Given this data, I want to generate columns G, H (say) with ID (12345) and the count of days that are between (not sure if this is the right term) with the above five (n) records. In the above example: days between (12/24 - 02/16) + days between (02/26 - 03/01).

A routine way is to write a vector with start, end dates (12/21/2009,12/22/2009, 12/23/2009,12/24/2009,12/25/2009,12/26/2009,12/27/2009,12/28/2009,12/29/2009) or (40168, 40169, .. etc,) UNION them and COUNT(DISTINCT). I dont know how to implement this in Excel and I would really appreciate any help.

Thanks a lot!!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I think I get it - coupla questions

1. Are you looking for formula, vb, or whatever works?
2. Will the matching IDs be grouped together or spread about in the dats
3. Could you supply a little more sample data, with a couple of ids & expected results?

HTH
 
Upvote 0
1. Probably VB .. I will deploy into SQL later
2. The output shall be ID, days. ID is only repeated once in the results.
3. Sorry, I dont have sample data ..

I think I get it - coupla questions

1. Are you looking for formula, vb, or whatever works?
2. Will the matching IDs be grouped together or spread about in the dats
3. Could you supply a little more sample data, with a couple of ids & expected results?

HTH
 
Upvote 0
You're right, it was interesting!

I made up some of my own and came up with this:

Code:
Sub getDayCount()
    'assumptions: header = yes, id data starts A2, start date B2, end date C2
    'output to appear in columns G & H
    'multiple lines for each ID, not neccessarily grouped or in order
    Dim sd, a, c, d, f
    Application.ScreenUpdating = False
    'get list of unique ids
    'uses scripting dictionary method
    Set sd = CreateObject("scripting.dictionary")
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Range("A2:A" & lr)
        If Not sd.exists(c.Value) Then
            sd.Add c.Value, 1
        End If
    Next
    'displays this list in col G and sorts it
    Range("G1:H1") = Array("ID", "DAYS")
    Range("G2").Resize(sd.Count, 1) = WorksheetFunction.Transpose(sd.keys)
    Range("G1").CurrentRegion.Sort key1:=Range("G1"), Header:=xlYes
    'clear counter for next part
    sd.RemoveAll
    'pass through sorted list in G
    For Each c In Range("G2", Cells(Rows.Count, "G").End(xlUp))
        'find all occurences of current id in col A
        Set f = Columns(1).Find(what:=c.Value)
        a = f.Address
        Do
            'loop from start date to end date
            'if this date is not already in our list, then add it
            For d = f.Offset(, 1).Value To f.Offset(, 2).Value
                If Not sd.exists(d) Then
                    sd.Add d, 1
                End If
            Next d
            Set f = Columns(1).FindNext(f)
            'exit loop if we've circled round and found our first instance again
        Loop While f.Address <> a
        'count items, write to sheet and clear counter for next run
        c.Offset(, 1) = sd.Count
        sd.RemoveAll
    Next c
End Sub
HTH

PS I tested the results using a very longwinded spreadsheet approach. The answer using your sample data (assuming I was right about that last start date) is 62
 
Last edited:
Upvote 0
Thanks much .. it works!! Will see if it works on actual data. Thanks again!

You're right, it was interesting!

I made up some of my own and came up with this:

Code:
Sub getDayCount()
    'assumptions: header = yes, id data starts A2, start date B2, end date C2
    'output to appear in columns G & H
    'multiple lines for each ID, not neccessarily grouped or in order
    Dim sd, a, c, d, f
    Application.ScreenUpdating = False
    'get list of unique ids
    'uses scripting dictionary method
    Set sd = CreateObject("scripting.dictionary")
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Range("A2:A" & lr)
        If Not sd.exists(c.Value) Then
            sd.Add c.Value, 1
        End If
    Next
    'displays this list in col G and sorts it
    Range("G1:H1") = Array("ID", "DAYS")
    Range("G2").Resize(sd.Count, 1) = WorksheetFunction.Transpose(sd.keys)
    Range("G1").CurrentRegion.Sort key1:=Range("G1"), Header:=xlYes
    'clear counter for next part
    sd.RemoveAll
    'pass through sorted list in G
    For Each c In Range("G2", Cells(Rows.Count, "G").End(xlUp))
        'find all occurences of current id in col A
        Set f = Columns(1).Find(what:=c.Value)
        a = f.Address
        Do
            'loop from start date to end date
            'if this date is not already in our list, then add it
            For d = f.Offset(, 1).Value To f.Offset(, 2).Value
                If Not sd.exists(d) Then
                    sd.Add d, 1
                End If
            Next d
            Set f = Columns(1).FindNext(f)
            'exit loop if we've circled round and found our first instance again
        Loop While f.Address <> a
        'count items, write to sheet and clear counter for next run
        c.Offset(, 1) = sd.Count
        sd.RemoveAll
    Next c
End Sub
HTH

PS I tested the results using a very longwinded spreadsheet approach. The answer using your sample data (assuming I was right about that last start date) is 62
 
Upvote 0
Hi Weaver,

Your code works great and it is fast too. I have a small request - is it possible to print the start and end dates it will use to calculate the days.

In other words, output shall be ID, start date (used for calculation), end date (used for calculation), days.

Also do you have any ideas how one can implement this in SQL?

Thanks again!

You're right, it was interesting!

I made up some of my own and came up with this:

Code:
Sub getDayCount()
    'assumptions: header = yes, id data starts A2, start date B2, end date C2
    'output to appear in columns G & H
    'multiple lines for each ID, not neccessarily grouped or in order
    Dim sd, a, c, d, f
    Application.ScreenUpdating = False
    'get list of unique ids
    'uses scripting dictionary method
    Set sd = CreateObject("scripting.dictionary")
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Range("A2:A" & lr)
        If Not sd.exists(c.Value) Then
            sd.Add c.Value, 1
        End If
    Next
    'displays this list in col G and sorts it
    Range("G1:H1") = Array("ID", "DAYS")
    Range("G2").Resize(sd.Count, 1) = WorksheetFunction.Transpose(sd.keys)
    Range("G1").CurrentRegion.Sort key1:=Range("G1"), Header:=xlYes
    'clear counter for next part
    sd.RemoveAll
    'pass through sorted list in G
    For Each c In Range("G2", Cells(Rows.Count, "G").End(xlUp))
        'find all occurences of current id in col A
        Set f = Columns(1).Find(what:=c.Value)
        a = f.Address
        Do
            'loop from start date to end date
            'if this date is not already in our list, then add it
            For d = f.Offset(, 1).Value To f.Offset(, 2).Value
                If Not sd.exists(d) Then
                    sd.Add d, 1
                End If
            Next d
            Set f = Columns(1).FindNext(f)
            'exit loop if we've circled round and found our first instance again
        Loop While f.Address <> a
        'count items, write to sheet and clear counter for next run
        c.Offset(, 1) = sd.Count
        sd.RemoveAll
    Next c
End Sub
HTH

PS I tested the results using a very longwinded spreadsheet approach. The answer using your sample data (assuming I was right about that last start date) is 62
 
Upvote 0
When you say start end dates, do you mean overall start/end or are you thinking about every time there's a break? The former would be relatively easy, not sure about the latter, although I should think it's possible. You'd have to cycle through sd.keys (which might need to be sorted first) and if the difference between the current and the previous is more than 1 then you print both, somewhere between these 2 lines
Code:
c.Offset(, 1) = sd.Count
sd.RemoveAll

As for SQL, I think that would be an entirely different approach, one I'd only use myself if the data was already in an Access database.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top