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

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
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:

mayday1

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

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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.
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221

ADVERTISEMENT

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.
 

mayday1

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,968
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top