Microsoft Excel - Rank on Multiple column - Asked By Rajender Prasad on 26-Jul-11 11:53 PM

Hi,

I have two cloumns say Cloumn R & Coulmn S.

Column R    Coulmn S
5          4.8
6          2.3
10          4.8
6.          2.3

I need to find out the rank for this.
Better if it ignore duplicates too.

I meant to say where 10 & 4.8 is the top performer.

Please help

Regards,
Prasad
Jitendra Faye replied to Rajender Prasad on 26-Jul-11 11:56 PM
You can do it by sorting as the following:

Follow this sample-

Select the columns A, B, and C

Use the sort columns with in this order
Column A : Ascending
Column B and C Descending

And you get what you need.

Or you can use this Macro to do it:

Columns("A:C").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlDescending, Key3:=Range("C2"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal


Hope this will help you.
Rajender Prasad replied to Jitendra Faye on 27-Jul-11 12:16 AM
I need only in Excel function not in VBA code.
Moreover, I need this values in Desired column only, i dont want to make any ascending desceding orders manually.

If I do that everything will be changed.

Sorry.

Prasad
TSN ... replied to Rajender Prasad on 27-Jul-11 12:19 AM
hi..
I need to rank a list based on two columns. So, first I have to rank all
the data based on column1 if value is above certain no. but if cells
have same values then rank those particular cells based on column2 and
once the value in column 1 is below that threshhold rank based on
column2 but if cells have same values (in column2) then rank those particular cells based on column1.

Here is how the logic flows....
If column A value is greater than 1000 then First rank by columnA, if
conflict in ranking (same value in column A) then rank by columnB, else
rank by columnB, if conflict in ranking (same value in columnB) then
rank by columnA again.

Here is an example
ColumnA....ColumnB.....Rank
4999....2.56......1 <<colA is greater than 1000, but rank based on colB
4999....1.59......2<<colA is greater than 1000, but rank based on colB
3149....3.59......3<<rank based on columnA
2482....0.00......4<<rank based on columnA
1712....0.00......5<<rank based on columnA
1422....0.73......6<<rank based on columnA
184......4.73......7<<colA smaller than1000, so rank based on colB
554......0.00......8<<colB has same values, rank using 4colA values only
377......0.00......9
298......0.00......10
196......0.00......11
and so on.....
I tried this but obviously it gives me same rank number where column A
and column B has same values...
=IF(A2>=1000,RANK(A2,$A$2:$A$42,0),RANK(B2,$B$2:$B $42,0)+COUNTIF($A$2:$A$42,">=1000"))

from this I get rank as following...
1
1
3
4
5
6
7
8
8
8
8
I hope this is clear enough...
Rajender Prasad replied to TSN ... on 27-Jul-11 01:40 AM
=IF(ISERROR((VLOOKUP(B9,'Audit Data'!$A$3:$S$176,MATCH(Audit!$D$6,'Audit Data'!$B$2:$U$2,0),0))*C9),"",(VLOOKUP(B9,'Audit Data'!$A$3:$S$176,MATCH(Audit!$D$6,'Audit Data'!$B$2:$Z$2,0),0))*C9)


is there anthing wrong in this formula, since it is not pulling the data from the Audit Data sheet.

Please help.

R,
Prasad
Pichart Y. replied to Rajender Prasad on 27-Jul-11 02:42 AM
Hi Rajender Prasad,

Here is an alternative one. This is arrays formula {=MATCH((R2+S2),(($R$2:$R$5)+($S$2:$S$5)),0)}, To create the kind of formula, just input normal formula =MATCH((R2+S2),(($R$2:$R$5)+($S$2:$S$5)),0), then instead of enter after finish your formula, press Ctrl+Shift+Enter then you will find the {   } arround your formula...try it...


here attachment ---> RankArray.zip

Pichart Y.

Rajender Prasad replied to Pichart Y. on 27-Jul-11 08:24 AM
Unable to open the attachment
Rajender Prasad replied to Pichart Y. on 27-Jul-11 08:24 AM
Unable to open the attachment
Pichart Y. replied to Rajender Prasad on 27-Jul-11 09:43 AM
Hi Prasad,


I attach here again---> RankArray.zip
Radhika roy replied to Rajender Prasad on 27-Jul-11 10:07 AM

You can use Rank Function.


Follow this sample Macro-

Sub Rank() 

    Columns("A:A").Insert 
    Range("A1", "A" & ActiveSheet.UsedRange.Rows.Count).Formula = "=ROW()" 
    Range("A1").EntireColumn.Copy 
    Range("A1").EntireColumn.PasteSpecial xlPasteValues 
    Columns("A:E").Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _ 
    , Order2:=xlDescending, Key3:=Range("D2"), Order3:=xlDescending, Header _ 
    :=xlheader, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ 
    , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ 
    xlSortNormal 
    Range("E2", "E" & ActiveSheet.UsedRange.Rows.Count).Formula = "=ROW()-1" 
    Range("E1").EntireColumn.Copy 
    Range("E1").EntireColumn.PasteSpecial xlPasteValues 
    Columns("A:E").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Columns("A:A").Delete 
    Range("A1").Select 
End Sub 

Follow these links also-

http://www.ozgrid.com/forum/showthread.php?t=31676&page=1
http://www.computing.net/answers/office/excel-rank-function/3431.html
http://excel.bigresource.com/Ranking-multiple-columns-and-second-place-PnfVewmC.html

Hope this will help you.
Rajender Prasad replied to Pichart Y. on 27-Jul-11 10:43 PM
I input this formula, and hit on Ctrl+Shift+Enter but no output is coming, and formula remails the same in the cell.
I did not find any paranthesis around the formula.

Please help.

R'
prasad
Rajender Prasad replied to Pichart Y. on 27-Jul-11 10:59 PM
I got the output but but it is giving serial number like 1 to 23 if I drag this formula to till 23rd row.

Moreover, it is muchbetter if I get the 1 Rank who is Top in the both columns. In your sheet it is opposite.

Please help.

R'
Prasad
Pichart Y. replied to Rajender Prasad on 28-Jul-11 01:11 AM
Hi Prasad,

I think I have made a mistake in my last formula, then this new one I use Coding...
here attachment, try it  --->RankNotConsiderDupp.zip
-----------------------------------------

Sub RankNotConsiderDupp()

DataLstRow = Range("R65536").End(xlUp).Row
For Each cell In Range("IA2:IA" & DataLstRow)
cell.Value = Range("R" & cell.Row).Value + Range("S" & cell.Row).Value
Next
For Each cell In Range("IB2:IB" & DataLstRow)
cell.Value = Application.WorksheetFunction.Large(Range("IA2:IA" & DataLstRow), cell.Row - 1)
    If cell.Address = "$IB$2" Then
    cell.Offset(0, 1).Value = 1
    ElseIf cell.Value = cell.Offset(-1, 0).Value Then
    cell.Offset(0, 1).Value = cell.Offset(-1, 1).Value
    Else
    cell.Offset(0, 1).Value = cell.Offset(-1, 1).Value + 1
    End If
Next
For Each cell In Range("T2:T" & DataLstRow)
cell.Value = Application.WorksheetFunction.VLookup(Range("IA" & cell.Row), Range("IB2:IC" & DataLstRow), 2, 0)
Next
Range("IA:IC").ClearContents
Range("T2").Select
End Sub
---------------------------------------

Pichart Y.

Rajender Prasad replied to Pichart Y. on 28-Jul-11 01:41 AM
Is this possible without using VBA code.

I mean only using excel  functions.

R'
Prasad
Rajender Prasad replied to Pichart Y. on 28-Jul-11 02:00 AM
Can u please explain what are these IA2:IA range & IB2:IB  & IA: IC

since my actual numbers are in the coulmn of Q5 & T5.

need output in B5.

Sorry for asking everytime.

Am very much new to this.


R'
Prasad
Rajender Prasad replied to Rajender Prasad on 28-Jul-11 07:06 AM
pls reply.. am stuck.

~Prasad
Pichart Y. replied to Rajender Prasad on 28-Jul-11 09:35 AM
Hi Prasad,

Never mind , when we don't know, then we ask...this is the quickest way to get knowledge, isn't it? 
I also am like you ha  ha...
To explain what you ask, when you see "IA:IA" in my code, it is the temp range that I use to calculate, then after calculation is done, I clear them with the code Range("IA:IC").clearcontents...by the way this new alternative much more easier for you...

Now I design the formula, but is need 3 more columns to use the last column in the right, to avoid any use cell in you spreadsheet, they are column IA,IB and IC.

To get the result  we need 4 steps..

1) I think that If we rank with 2 criteria, both are number, then number1+number2, no matter what the number are, the ranking will reflex out correctly. Then in column IA I input the formula =Q2+T2....simple like this

2) Now to arrange the number in IA, I use function small() then the formula is =LARGE($IA$2:$IA$10,ROW()-1)...then our data will be sort by Highest value to lowest one.

3) then in Column IC I assign the ranking with the formula =IF(IB1="Sort",1,IF(IB2=IB1,IC1,IC1+1))
to check if the value in IB= text "Sort" we know that this is the first number then we put 1 in the cell
But if else we check that the cell in the left equal to the above cell of it, then assign the same number (number above)
then if the the cell in the left is not equal to the above cell of it, then assign that same number+1

4 finally use Vlookup() to get the ranking to our area =VLOOKUP(IA2,IB:IC,2,0)


Hope this is more easy for you....

Pichart Y.



Date Format - Rajender Prasad replied to Pichart Y. on 06-Sep-11 05:01 AM
Example date format.zipHi Pichart,

Hope you are good.

I have a query.

In the attached sheet, I need the date format should be 8/7/2011 14:30 (Column F) even after I copy paste the date in anyother formats like 06 Aug 2011 02:30 PM (Column E) etc..
It should be unique this helps me to calculate the difference of two times.

Column should also not entertine any other format except 05-Aug-2011 even you give the input in anyway.

Please help.

Pichart Y. replied to Rajender Prasad on 06-Sep-11 05:28 AM
Hi Prasad,

I am fine... thank you, hope you are fine, too.

With formating, if you copy then paste to another cell (paste all), the destination cell's format will be change...if you want  to paste new value, but keep the destination format, you must paste value, then destination cell's format will not be changed.

Or you need to input the data to cell (column) of date as "Text", this will always keept the format, but you can not work "Date field" as date series... For example in you use pivot table which has feature to group the date to be month, quarter or year, etc, but if we keep the data as text, excel cannot provide this group information.

Pichart Y.

Rajender Prasad replied to Pichart Y. on 07-Sep-11 01:43 AM
hi,

Destination format should be always 8/7/2011 14:30, not the matter of where i have copied from that date.
It can be any application or webiste etc..

Please give solution fo rthis.

R'
prasad