More summarizing....

[mayday1]

OK, I've got a long list of patients. Each row show ID Number, start date, end date (which may be blank if treatment is still going on). An ID number may appear once or in 20 rows for reasons that aren't relevant here. If an ID number does appear more than once, the start date and end dates area always the same and everything else can be ignored.

Sample data:
111111111, 1/1/14, 1/5/14
111111111, 1/1/14, 1/5/14
111111111, 1/1/14, 1/5/14
222222222, 1/1/14,
222222222, 1/1/14,
333333333, 1/3/14, 1/8/14
333333333, 1/3/14, 1/8/14
444444444, 1/2/14. 1/12/14
555555555, 1/5/14.

What I need is a summary that shows, for every date starting with the earliest start date in the file and going up to the current date:
The Date and:
- How many of these records were started on this date?
- How many were flagged as closed on this date?
- What is the highest number of records open at any time on this date?
- What is the average number of days that those open at any point on this date existed?
- What is the median number of days that those open at one point on this date existed? This one seems especially hard and I could do this manually if nobody can figure it out.
- How old is the oldest record that was open at some point on this date?
- What is the lowest number in days that a record was open on this date?

So for the Sample data above it would look something like this: (I’m making up all the numbers)
1/1/14, 5, 0, 5, 1, 1, 1,1
1/2/14 1,0,6,.8,2,2.1
1/3/14 2,0,8,2.4,3,3,1
1/4/14 0,0,8,3, 4,4,2 (There were no records started on this date but I still need the data for all other records.)
1/5/14 etc....

 

[ztodd]

Are you familiar with pivot tables? Start messing around with pivot tables- you may need more than one. Maybe do a web search for an excel pivot table tutorial.

or if you don't want to use pivot tables, you can use the min function to find first date to start with, then fill down a function adding 1 to that to list more dates, then use the countif or countifs function for your other report columns.

For the average column you can use sumifs along with countifs.

 

[mayday1]

Some of this looks like I should be able to get the info with a Pivot Table or a few pivot tables - at least I think a pivot table should be able to do this but I sure don't see how. Anyway, what I'm looking for is a macro that I can give to a volunteer who doesn't know Excel so they can run this every day to get the reports we need.

 

[Aladin Akyurek]

Care to word how you obtain

1/2/14 1,0,6,.8,2,2.1

one by one given the questions?

 

[ztodd]

The median column would be a bit tougher- you could use an array formula, using the median function and the if function operating on ranges of cells:

=median(if(and([datasheet!B:B]<=[reportsheet!a1],[datasheet!C:C]>=[reportsheet!a1]),value([reportsheet!a1])-value([datasheet!B:B]),""))

You would need to press F2 on the cell to put this formula in, then paste this formula, then hold down control and shift and press Enter

For the oldest record you could also use array formula-

=value([reportsheet!a1])-value(min(if(and([datasheet!B:B]<=[reportsheet!a1],[datasheet!C:C]>=[reportsheet!a1]),[datasheet!B:B],""))

See if you can learn enough about array formulas and these functions to figure out what I'm doing to see if you can apply it to the lowest # days open.

 

[NeonRedSharpie]

Sub getAllTheDataPoints()

    Dim endRow As Long
    Dim startRow As Integer
    
    startRow = 2
    
    ActiveSheet.Range("$A:$C").RemoveDuplicates _
    Columns:=Array(1, 2, 3), Header:=xlYes


    Cells(1, 5).value = "Date"
    Cells(1, 6).value = "Count Start"
    Cells(1, 7).value = "Closed On"
    Cells(1, 8).value = "Max Open"
    Cells(1, 9).value = "Average Length"
    Cells(1, 10).value = "Median Length"
    Cells(1, 11).value = "Oldest Open"
    Cells(1, 12).value = "Youngest Open"
    
    Columns(5).value = Columns(2).value
    
    ActiveSheet.Range("E:E").RemoveDuplicates _
    Columns:=Array(1), Header:=xlYes
        
    endRow = 4 'Cells(Rows.count, 5).End(xlUp)


    With Range(Cells(startRow, 6), Cells(endRow, 6))
        .FormulaR1C1 = "=CountIF(C2,RC5)"
    End With
    
    With Range(Cells(startRow, 7), Cells(endRow, 7))
        .FormulaR1C1 = "=CountIF(C3,RC5)"
    End With
    
    With Range(Cells(startRow, 8), Cells(endRow, 8))
        .FormulaR1C1 = "=COUNTIFS(C3,"">="" & RC5,C2,""<="" & RC5) + COUNTIFS(C3,""="" & """" , C2 , ""<="" & RC5)"
    End With
    


End Sub

Here's where I got so far. This assumes all data in columns A:C. The final four columns I'll need to add in helper columns or other formulas, but this should get the structure of the macro down.

 

[ztodd]

Sub getAllTheDataPoints()

    Dim endRow As Long
    Dim startRow As Integer
    
    startRow = 2
    
    ActiveSheet.Range("$A:$C").RemoveDuplicates _
    Columns:=Array(1, 2, 3), Header:=xlYes


    Cells(1, 5).value = "Date"
    Cells(1, 6).value = "Count Start"
    Cells(1, 7).value = "Closed On"
    Cells(1, 8).value = "Max Open"
    Cells(1, 9).value = "Average Length"
    Cells(1, 10).value = "Median Length"
    Cells(1, 11).value = "Oldest Open"
    Cells(1, 12).value = "Youngest Open"
    
    Columns(5).value = Columns(2).value
    
    ActiveSheet.Range("E:E").RemoveDuplicates _
    Columns:=Array(1), Header:=xlYes
        
    endRow = 4 'Cells(Rows.count, 5).End(xlUp)


    With Range(Cells(startRow, 6), Cells(endRow, 6))
        .FormulaR1C1 = "=CountIF(C2,RC5)"
    End With
    
    With Range(Cells(startRow, 7), Cells(endRow, 7))
        .FormulaR1C1 = "=CountIF(C3,RC5)"
    End With
    
    With Range(Cells(startRow, 8), Cells(endRow, 8))
        .FormulaR1C1 = "=COUNTIFS(C3,"">="" & RC5,C2,""<="" & RC5) + COUNTIFS(C3,""="" & """" , C2 , ""<="" & RC5)"
    End With
    


End Sub

Here's where I got so far. This assumes all data in columns A:C. The final four columns I'll need to add in helper columns or other formulas, but this should get the structure of the macro down.

Good start, but in my opinion it's better to put the report on a separate sheet, so that they can filter the original data on one sheet, and can switch to another sheet to see the report.

 

[mayday1]

Sorry for the delay in responding - family emergency.
So far, THIS LOOKS AWESOME!!! I tried it on some sample data and t's so fast. I'm looking at your code and I think this will be a great one for me to learn this stuff. Is the rest possible?
Thank you, thank you!

 

[Aladin Akyurek]

Sorry for the delay in responding - family emergency.
So far, THIS LOOKS AWESOME!!! I tried it on some sample data and t's so fast. I'm looking at your code and I think this will be a great one for me to learn this stuff. Is the rest possible?
Thank you, thank you!

Possibly, if you explain things as requested in post #4, for example...

 

[ztodd]

Look up the help about array formulas online... I posted in another thread about how array formulas work if you want to look at that, if it helps you. Not sure if I actually tested the formulas that I posted in this thread- might need to revise those to kind of fit what I did in the other thread... http://www.mrexcel.com/forum/excel-questions/807411-date-range-conflict-checker.html