Hi manik.nag
I am surprised that nobody has replied to you already. I have had a quick look at your explanation and files.
Download the attached file and run the macro there to see what you think.
Your priority understandably is to target Steps 5 to 7 and so that is the purpose of this first macro.
Attached is cut-down version of your file (Jan data only) and includes a sheet containing the quintile values from the other file (easier to test everything in single file). I had to add the values for 1 to 5 which were not in the original table (I may have guessed incorrectly but easy to amend) - there are ranges with fewer than 5 rows and the macro would have failed without valid values in the table
IMPORTANT NOTE - Columns A and B (in the file you attached) are identical. They should be different. One should be the week and the other the year. Therefore the results differ from yours. When you put in the data you must have had the correct values, but something probably happened to the file after that. Please amend your original file and attach it again, so that the data I use for testing is valid.
What the macro does
-sorts the data by "week" , by "queue", and then by "% in column I" (should also add the "month" to this later)
-determines each range that needs to be ranked
-based on the number of rows in each range goes to quintile table and pastes relevant values to column P(test column) in sheet "Jan"
- at the end of the macro a message is dumped to screen to summarise everything. There is also a proof that the rows dealt with separately in each queue range tally to the total of all the rows.
Is this close to what you are looking for?
Sub ApplyQuintileValues()
'declare variables
Dim LastRow As Long, NumberOfRows As Long, RangeRowCount As Long, r As Integer, RangeStart As Integer, RangeEnd As Integer
Dim QueueRange As Range, FirstCell As Range, LastCell As Range, quintileRange As Range
Dim QueueRangeDetails As String, Queue1 As String, Queue2 As String
'sort the data
With Sheets("Jan")
LastRow = .Range("C2").End(xlDown).Row 'last row in sheet "Jan"
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("C2:C" & LastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("I2:I" & LastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
End With
With Sheets("Jan").Sort
.SetRange Range("A1:P" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'determine the Queue range
With Sheets("Jan")
Queue1 = .Range("C2").Value
Queue2 = Queue1
For r = 2 To LastRow 'r is the row number
RangeStart = r
Do Until Queue2 <> Queue1
Queue2 = .Cells(r, 3).Offset(1, 0).Value
r = r + 1
Loop
Queue1 = Queue2
r = r - 1
RangeEnd = r
Set QueueRange = .Range("C" & RangeStart & ":C" & RangeEnd)
NumberOfRows = QueueRange.Rows.Count
'build information whilst testing only - for message box at end of code
QueueRangeDetails = QueueRangeDetails & vbNewLine & QueueRange.Address(0, 0) & " = " & NumberOfRows & " rows"
RangeRowCount = RangeRowCount + NumberOfRows
'determine copy from range, copy relevant quintile values and paste to column P
'(after testing is complete - amend to paste to column O)
Set FirstCell = Sheets("Quintile").Cells(2, NumberOfRows)
Set LastCell = Sheets("Quintile").Cells(NumberOfRows + 1, NumberOfRows)
Set quintileRange = Range(FirstCell, LastCell)
quintileRange.Copy
.Range("P" & RangeStart).PasteSpecial xlAll
Next r
End With
MsgBox "Queue Ranges and row counts" & vbNewLine & QueueRangeDetails & vbNewLine _
& "Total of Queue Row Counts = " & RangeRowCount & vbNewLine _
& "Total of all rows = " & LastRow - 1
End Sub
NB - this macro will not work in your original file because I have added a new sheet for Jan and also the table of quintile values
Gradation - Quantile Performence
Reply With Quote
