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!!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
PS I'm assuming the last start date should be 12/24/2009
 

murthy_v_s

Board Regular
Joined
Mar 2, 2007
Messages
83

ADVERTISEMENT

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
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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:

murthy_v_s

Board Regular
Joined
Mar 2, 2007
Messages
83

ADVERTISEMENT

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
 

murthy_v_s

Board Regular
Joined
Mar 2, 2007
Messages
83
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
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,525
Messages
5,511,843
Members
408,864
Latest member
cmajewsk

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top