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

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

Follow this sample-

Column A : Ascending

Column B and C Descending

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.

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

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

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

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

from this I get rank as following...

1

1

3

4

5

6

7

8

8

8

8

I hope this is clear enough...

=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

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

Please help.

R,

Prasad

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.

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.

Unable to open the attachment

Unable to open the attachment

You can use Rank Function.

Follow this sample Macro-

Sub Rank()

Columns("A:A").Insert

Range("A1", "A" & ActiveSheet.UsedRange.Rows.

Range("A1").EntireColumn.Copy

Range("A1").EntireColumn.

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.

Range("E1").EntireColumn.Copy

Range("E1").EntireColumn.

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

http://www.ozgrid.com/forum/

http://www.computing.net/

http://excel.bigresource.com/

Hope this will help you.

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

I did not find any paranthesis around the formula.

Please help.

R'

prasad

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

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

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

-----------------------------------------

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.

Is this possible without using VBA code.

I mean only using excel functions.

R'

Prasad

I mean only using excel functions.

R'

Prasad

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

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

pls reply.. am stuck.

~Prasad

~Prasad

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)

attachment--->RankNotConsiderDupp.zip

Hope this is more easy for you....

Pichart Y.

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.

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.

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.

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.

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

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