More summarizing....

mayday1

Board Regular
Joined
Oct 5, 2007
Messages
241
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....
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Code:
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.
 
Upvote 0
Code:
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.
 
Upvote 0
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!
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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