# More summarizing....

#### mayday1

##### Board Regular
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

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

##### MrExcel MVP
Care to word how you obtain

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

one by one given the questions?

#### ztodd

##### Board Regular

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
Code:
``````Sub getAllTheDataPoints()

Dim endRow As Long
Dim startRow As Integer

startRow = 2

ActiveSheet.Range("\$A:\$C").RemoveDuplicates _

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 _

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

Code:
``````Sub getAllTheDataPoints()

Dim endRow As Long
Dim startRow As Integer

startRow = 2

ActiveSheet.Range("\$A:\$C").RemoveDuplicates _

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 _

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

##### MrExcel MVP
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...

Replies
3
Views
37
Replies
18
Views
122
Replies
1
Views
47
Replies
6
Views
117
Replies
5
Views
53