# 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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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:
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.

Care to word how you obtain

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

one by one given the questions?

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.

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.

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.

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!

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
4
Views
579
Replies
9
Views
487
Replies
3
Views
415
Replies
4
Views
681
Replies
2
Views
289

1,221,525
Messages
6,160,329
Members
451,637
Latest member
hvp2262

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

### Which adblocker are you using?

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

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