Ranking Multiple Columns

[holeshot]

Here is an example of a report I run every month, What I do is once I get the
Data Listed Below, I add in the 1-5, 6-12, & the 13+ columns, Then I Sort the
Data by RESPAR then by DAYS, then I manually Count the Days 1-5 to get a
Count of Days between 0 & 5 days, then the same for 6-12 days & 13+ days. I
do this by putting a 1 in the First record row with a 0 then hold down the
Ctrl KEy and dragging till I reach a day of 6, then start that column over,
going to 13. I have about 2000 or more records every month.

Is there a formula I can use that will do this for me, with out having to
Manually fill these days in?

STNO WONO DAYS RESPAR HDRDIV 1-5 6-12 13+
0 K-33782 0 CM G 1
0 K-34413 0 CM G 2
0 K-34526 0 CM G 3
0 K-33425 1 CM G 4
0 K-34113 1 CM G 5
0 K-35059 1 CM G 6
0 K-34820 3 CM G 7
0 K-34771 4 CM G 8
0 K-34663 7 CM G 1
0 K-34469 9 CM G 2
0 K-31136 73 CM G 1
9 CM Average
11 CM Count

Thanks in Advance for your help!

 

[shelfish]

I don't know about a formula, but it sounds like a macro could handle
this pretty well. I'd take on the task for you but I am LOST on what
you are trying to accomplish.

You might try this. Go to tools, macro, record new macro. Do what you
normally do and then stop the recording. Post the results here so and
we can look over the code for optimization, etc.

Just a sugg.

S.

 

[Herbert Seidenberg]

For 6-12
=SUMPRODUCT((RESPAR="CM")*(DAYS>=6)*(DAYS<13))

 

[holeshot]

Herbert, That formula didn't work for me.

Shelfish, I think this is what you requested, this is how I get the Count of
How many Days 0-5, 6-12, & 13+. I am looking for a Formula that I can put in
those Columns 0-5, 6-12, & 13+. Than will count how many day are 0-5, 6-12 &
13+. I will need it for Multiple "RESPARS" which are already sorted per my
First Example.

CM has:
8 days between 0-5
2 days between 6-12
1 day over 13

Hope this make sense.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/5/2007
'

'

Range("K5").Select
ActiveCell.FormulaR1C1 = "1"
Selection.AutoFill Destination:=Range("K5:K12"), Type:=xlFillSeries
Range("K5:K12").Select
Range("L13").Select
ActiveCell.FormulaR1C1 = "1"
Selection.AutoFill Destination:=Range("L13:L14"), Type:=xlFillSeries
Range("L13:L14").Select
Range("M15").Select
Application.CommandBars("Stop Recording").Visible = False
ActiveCell.FormulaR1C1 = "1"
Range("N17").Select
End Sub