PDA

View Full Version : Gradation - Quantile Performence



manik.nag
05-09-2015, 12:47 PM
Dear all,
Presently I’m working with a file (Namely Quintile file)that contains 15 columns and 3000 rows .And doing the below activity on weekly basis on last Four Sheets in Quantile file.
Step 1 Paste the Queue wise data from column C to G from a different excel source file.
Step 2 Update Month and weekend date on column A & B
Step 3 Drag the formula from column H to K (available In previous rows)
Step 4 update the data from column L, M, and N from a different excel source file.
Step 5 Filter the header and select the weekly data from column B ( i.e. 1-May )
Step 6 Filter one single queue Name from Column C and sort Largest to smallest on Column “I”
Step 7 Take the count of rows and Grade them in Equal Gradation from Q1 to Q5 form a file called Quintile Format file(In Quintile format file I have created a Gradation from Q1 to Q5 in respect to count wise from count which I just map with the Quintile file to derive the Grades)
Step 8 Repeat the same activity from Step 6 (I.e. Filter another queue Name from Column C and sort Largest to smallest on Column “I” Take the count of rows and Grade them in Equal Gradation from Q1 to Q5
I want something to be done which should minimize this manual activity mainly from step 5 to Step 7 because I have to do it for 70 odd Queue every Week in 5 Excel sheet which took long time to complete this task.
With Regards
Manik Nag

Yongle
05-14-2015, 08:26 AM
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

manik.nag
05-14-2015, 11:13 AM
Hi Yongle
Thank you indeed for replying my query, I've checked the macro, it seems exactly what I wanted for, please allow me to do some test runs to come back to you with any further query's.
Thanks a trillion
Manik

Yongle
05-14-2015, 11:22 AM
Glad it works
We can also sort out the rest of your list later - nothing looks too difficult.

Yon

manik.nag
05-14-2015, 01:41 PM
Well, while I was doing some test runs I saw few cases where Queuewise row count 79, 88 and 97 ware not updated successfully( there might be other count possibility as well)
I also ran it for one queue name (but row count remains good amount) was also not running

Regards
Manik Nag

Yongle
05-14-2015, 04:00 PM
from post#02 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.


I cannot test everything fully without valid data.
Pease reply attaching updated MYO Queuewise...xlsm with valid week ending values in column B
many thanks
Yon

manik.nag
05-15-2015, 05:55 AM
Well yengle,
I've attached the data for your reference. I saw few cases where Queuewise ware not updated successfully,

With Regards Manik Nag

Yongle
05-15-2015, 06:59 AM
manik.nag

Thanks for the file
When back at my PC I will modify the code so that you can watch the macro update the workbook and follow what it is doing at each step.

Yon

Yongle
05-16-2015, 04:17 AM
Hi manik.nag

I have made good progress. The code is not the problem.

On the lines that are causing the problem, the Queue Name looks exactly the same as the Queue Name in cell below but VBA sees the 2 cells differently.
When I copied and pasted so that the cells are truly identical the code works properly.

I will try a few things to see if I can fix the data with VBA before running the rest of the code or else determine what is different about the 2 cells, in which case it should be possible to modify the code.

regards
Yon

Yongle
05-16-2015, 11:59 AM
Hi manik.nag

I can confirm that the code is not the problem.
Problem
The problem was in the Quintile Table - the code was looking at (for example) column 254 if row count was 254 - but the table was not complete.
Solution
I have created a new quintile table (using vba) with all the values for row counts from 1 to 500 (vba is in attached file)
I was not sure what to do if the row count does not divide by 5 and so have assumed if remainder is
1 = Q1
2 = Q1 Q2
3 = Q1 Q2 Q3
4 = Q1 Q2 Q3 Q4
Is this correct? If not, what do we do?

Please check all is ok by running macro ApplyQuintileValues

manik.nag
05-18-2015, 03:02 AM
Hi Yon
there are two situation one for even numbers, the distribution is 20% each i.e. even numbers of row/5 but the challenges for Odd numbers are only to be mapped from the Quintile table (i.e. the logic is for odd number like 7 is below



Q1

1



Q2

1



Q3

2



Q4

2



Q5

1



Quintile

7




And for 9 is below


Q1

2



Q2

2



Q3

1



Q4

2



Q5

2



Quintile

9




Actually for odd numbers quintile are distributed as even as possible in between Q1, Q2, and Q5 and remaining are distributed in Q3 then Q4
Regards manik

Yongle
05-18-2015, 07:56 AM
A quintile table should divide everything into 5 equal groupings, but where not divisible by 5 deal consistently with the remainder (=1, 2 , 3 or 4)

I checked your original quintile table to understand what you said in post#11 and think the table contains several errors.

For Example - in your table the values for 21 =
Q1 Q1 Q1 Q1 / Q2 Q2 Q2 Q2 / Q3 Q3 Q3 Q3 Q3 / Q4 Q4 Q4 Q4 Q4 / Q5 Q5 Q5
I think 21 should be:
Q1 Q1 Q1 Q1 / Q2 Q2 Q2 Q2 / Q3 Q3 Q3 Q3 Q3 / Q4 Q4 Q4 Q4 / Q5 Q5 Q5 Q5
( 20 divided by 5 = 4 with one left over which goes to Q3)

Where next?
To rebuild the table, we need one consistent rule

The table requires rebuilding to a consistent formula
So if the pattern for the remainder is
1 = Q3
2 = Q2 Q4
3 = Q1 Q3 Q5
4 = Q1 Q2 Q4 Q5

then
06 = Q3 + 1 X Q1 Q2 Q3 Q4 Q5
07 = Q2 Q4 + 1 X Q1 Q2 Q3 Q4 Q5
08 = Q1 Q3 Q5 + 1 X Q1 Q2 Q3 Q4 Q5
09 = Q1 Q2 Q4 Q5 + 1 X Q1 Q2 Q3 Q4 Q5



and
11 = Q3 + 2 X Q1 Q2 Q3 Q4 Q5
12 = Q2 Q4 + 2 X Q1 Q2 Q3 Q4 Q5
13 = Q1 Q3 Q5 + 2 X Q1 Q2 Q3 Q4 Q5
14 = Q1 Q2 Q4 Q5 + 2 X Q1 Q2 Q3 Q4 Q5



and
16 = Q3 + 3 X Q1 Q2 Q3 Q4 Q5
17 = Q2 Q4 + 3 X Q1 Q2 Q3 Q4 Q5
18 = Q1 Q3 Q5 + 3 X Q1 Q2 Q3 Q4 Q5
19 = Q1 Q2 Q4 Q5 + 3 X Q1 Q2 Q3 Q4 Q5



Is the above pattern correct?

manik.nag
05-20-2015, 06:45 AM
Hi Yon sorry for replying late.
pattern will be
1 = Q1
2 = Q1 Q2
3 = Q1 Q2 Q3
4 = Q1 Q2 Q3 Q4
5 = Q1 Q2 Q3 Q4 Q5

then
06 = Q3 + 1 X Q1 Q2 Q3 Q4 Q5
07 = Q3 Q4 + 1 X Q1 Q2 Q3 Q4 Q5
08 = Q3 Q4 + 2 X Q1 Q2 Q5
09 = Q3 + 2 X Q1 Q2 Q4 Q5



and
11 = Q3 + 2 X Q1 Q2 Q3 Q4 Q5
12 = Q3 Q4 + 2 X Q1 Q2 Q3 Q4 Q5
13 = Q3 Q3 Q4 Q4 + 3 X Q1 Q2 Q5
14 = Q3 Q3 + 3 X Q1 Q2 Q4 Q5



and
16 = Q3 + 3 X Q1 Q2 Q3 Q4 Q5
17 = Q3 Q4 + 3 X Q1 Q2 Q3 Q4 Q5
18 = Q1 Q2 Q5 + 3 X Q1 Q2 Q3 Q4 Q5
19 = Q1 Q2 Q4 Q5 + 3 X Q1 Q2 Q3 Q4 Q5
And the rule is the row number count is divided by 5 and distribution goes equally between Q1 to Q5 then remaining numbers goes equally between Q3 and Q4
Regards
Manik nag

Yongle
05-20-2015, 08:04 AM
Thanks for reply,
I will now amend the code to generate the table correctly. I have re-written what was included in your post#13 and it forms a regular pattern after the first 5 - so that is easy to write.

To provide a regular pattern your post#13 is the same as
1 = Q1
2 = Q1 Q2
3 = Q1 Q2 Q3
4 = Q1 Q2 Q3 Q4
5 = Q1 Q2 Q3 Q4 Q5


then
06 = Q3 + 1 X Q1 Q2 Q3 Q4 Q5
07 = Q3 Q4 + 1 X Q1 Q2 Q3 Q4 Q5
08 = Q1 Q2 Q5 + 1 X Q1 Q2 Q3 Q4 Q5
09 = Q1 Q2 Q4 Q5+ 1 X Q1 Q2 Q3 Q4 Q5




and
11 = Q3 + 2X Q1 Q2 Q3 Q4 Q5
12 = Q3 Q4 + 2X Q1 Q2 Q3 Q4 Q5
13 = Q1 Q2 Q5 + 22X Q1 Q2 Q3 Q4 Q5
14 = Q1 Q2 Q4 Q5 + 2X Q1 Q2 Q3 Q4 Q5






and
16 = Q3 + 3X Q1 Q2 Q3 Q4 Q5
17 = Q3 Q4 + 3X Q1 Q2 Q3 Q4 Q5
18 = Q1 Q2 Q5 + 3X Q1 Q2 Q3 Q4 Q5
19 = Q1 Q2 Q4 Q5 + 3X Q1 Q2 Q3 Q4 Q5

Yongle
05-20-2015, 09:21 AM
Attached is the workbook with a re-calculated Quintile table (values 1 to 500). It is based (hopefully!!) on your instructions in post#13.
I suggest you:
1 Check the Quintile table is correct
2 Add lots of test data to sheet Jan the workbook and run the macro ApplyQuintileValues.

When you are satisfied that all is correct, then we can automate the other steps in your list

regards
Yon

Yongle
05-20-2015, 01:53 PM
The Other Steps

AFTER you have tested/checked file attached to my post#15, a few questions in respect of STEPS 1 - 4


Step 1 Paste the Queue wise data from column C to G from a different excel source file.

- name of source workbook (including .xl..) ?
- name of worksheet ?
- in which columns is data ?
- how do you choose the range - is it all the rows? or some of the rows? if some, how do you identify which rows?



Step 2 Update Month and weekend date on column A & B
- do you type this in and copy it down?



Step 3 Drag the formula from column H to K (available In previous rows)
- no questions



Step 4 update the data from column L, M, and N from a different excel source file.
- Is this the same source file as in Step 1
(I think it is the same file, otherwise it would be a big job matching the names in each row)
- (if it is same file) in which columns is data ?
- if a different source file, we will tackle later!

I will be busy on another project for the next 2 days. So there is no need to hurry!
What you are asking is possible and not too complicated.
It would also help, I think, if you could attach the source file(s) so that I can test everything before sending you the code.
thanks

manik.nag
05-21-2015, 06:30 AM
I've checked your file and this time it's a BIG Success. a huge THANK YOU. :bow::clap:
Another thing, I am uploading the final file where you can see that I'm summarising the snapshot queue wise by creating a pivot
my request to you if that also can be incorporated in the macro project It will be a great solution for me
please note the Pivot criteria below
Quintile in Row Area
Month and WE in The Pivot column levels
Baseline and OTR% in value field (formulla in pivot calculated field is Baseline ='Target Avg'/'Total Volume'| OTR% ='Closed Cases'/'Total Volume'
WAHT =Talktime/'Total Volume')

Answering to your question
Step 1
Source file
05 - May Sales Offline Report till dd mmm yy
05 - May My Orders Report till dd mmm yy
Worsheet name "Queue Wise"
in which columns is data ? variable since I select the required data from a pivot table and I add or remove field as per my requirment
how do you choose the range - is it all the rows? or some of the rows? if some, how do you identify which rows?
all the rows
Step 2
do you type this in and copy it down?
Type the first cell then copy it down
Step 4
Is this the same source file as in Step 1
(I think it is the same file, otherwise it would be a big job matching the names in each row)
- (if it is same file) in which columns is data ?
- if a different source file, we will tackle later!
It's a different file chenged every week
I'm also keen to automate the entire activity
and all the best for your project.
Regards Manik Nag

Yongle
05-21-2015, 09:29 AM
I have made a note that you now want the vba to also produce a pivot table for you - we will deal with that later
.
I did not expect what you are doing to be quite so complicated - hopefully we can make it a lot easier for you.
To automate what you are doing manually , I need to see the raw data.
VBA needs to be told exactly what to do, and where to get every piece of information from.
Also we must make this as simple if possible - otherwise if something small changes, it will be more difficult to modify the code in the future.
I am hoping that the items below will provide me with everything I need.
thanks

*** Question Do you have 2 files for every "week ending" with names like 05 - May Sales Offline Report till dd mmm yy & 05 - May My Orders Report till dd mmm yy and are they all in the same folder ?



Step 1 Source file = 05 - May Sales Offline Report till dd mmm yy & 05 - May My Orders Report till dd mmm yy

*** Please attach a copy of both files to your reply
05 - May Sales Offline Report till dd mmm yy" & 05 - May My Orders Report till dd mmm yy



Step 4 ....It's a different file chenged every week

*** Please attach a copy this file too

manik.nag
06-03-2015, 08:27 AM
I'm sorry for replying late.
yes they are in the same folder and May Sales Offline Report till dd mmm yy & 05 - May My Orders Report till dd mmm yy reports structure's are identical(with Agents name and values are different)

Step 1 I change the pivot fields as per the criteria(i.e. select WE 29/05/2015 in the Report filter select Queue and EIn in the Row Lebels and select Sum of TotCloVol, Sum of Overall Worked Volume and Sum of WOTR Target in the Value area



With Regards
Manik Nag