I need to rank a list based on two columns. So, first I have to ran
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 thos
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 o
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 value
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:$
$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...
Thanks, Ja

--
sa0200
-----------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=53340

this worked with your 11 values in A2:A12

=MATCH(IF(A21000,A2,B2+0.000001*A2),LARGE(IF($A$2 :$A$121000,$A$2:$A$12,$B$2:$B$12+0.000001*$A$2:$A $12),{1,2,3,4,5,6,7,8,9,10,11}),0)

Entered with Ctrl+Shift+Enter Rather than just enter in C2, then drag filled
down to C11.

--
Regards,
Tom Ogilvy


"sa02000" wrote:


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...
Thanks, Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533404

Tom, Thanks for the reply... but I have 1000s of rows for which I need
to do the ranking. Sorry I didn't say that explicitly in my original
post. And this no. of rows changes from month to month.....so even if I
put a big sequence for one month next month it will be off....any other
ideas or can this be modified to do ranking for 1000s of rows??


Jay
PS: I don't understand this formula and it didn't quite work for me....
:(


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533404

I created a dummy column with this formula

=IF(E2=1000,RANK(E2,$E$2:$E$122,0)+RANK(I2,$I1:$I $122,0)/100,RANK(I2,$I$2:$I$122,0)+RANK(E2,$E$2:$E$122,0)/1000+COUNTIF($E$2:$E$122,"=1000"))

and then ranked this dummy column to get my actual ranking
=RANK(K2,$K$2:$K$122,1)

I used help from this page by chris Pearson....
http://www.cpearson.com/excel/rank.htm

Thanks for all those who replied to this and other similar posts.

Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533404

I created a dummy column with this formula

=IF(E2=1000,RANK(E2,$E$2:$E$122,0)+RANK(I2,$I1:$I $122,0)/100,RANK(I2,$I$2:$I$122,0)+RANK(E2,$E$2:$E$122,0)/1000+COUNTIF($E$2:$E$122,"=1000"))

and then ranked this dummy column to get my actual ranking
=RANK(K2,$K$2:$K$122,1)

I used help from this page by chris Pearson....
http://www.cpearson.com/excel/rank.htm

Thanks for all those who replied to this and other similar posts.

Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533404

I created a dummy column with this formula

=IF(E2=1000,RANK(E2,$E$2:$E$122,0)+RANK(I2,$I1:$I $122,0)/100,RANK(I2,$I$2:$I$122,0)+RANK(E2,$E$2:$E$122,0)/1000+COUNTIF($E$2:$E$122,"=1000"))

and then ranked this dummy column to get my actual ranking
=RANK(K2,$K$2:$K$122,1)

I used help from this page by chris Pearson....
http://www.cpearson.com/excel/rank.htm

Thanks for all those who replied to this and other similar posts.

Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533404

You can generate the sequence

with something like

=row(1:1000)

for example, in a new cell put in

=row(1:100)

then select row(1:100) and hit F9. then escape to return to the formula.

If can be made dynamic. Put some data in A1:A15, then enter this formula
elsewhe

=row(indirect("1:" & counta(A1:A100)))

Now select
row(indirect("1:" & counta(A1:A100)))

and hit F9.

Actually, I was going to ask if you could use a dummy column - it is
certainly easier - much simpler I think you will agree.

--
Regards,
Tom Ogilvy


"sa02000" wrote:


I created a dummy column with this formula

=IF(E2=1000,RANK(E2,$E$2:$E$122,0)+RANK(I2,$I1:$I $122,0)/100,RANK(I2,$I$2:$I$122,0)+RANK(E2,$E$2:$E$122,0)/1000+COUNTIF($E$2:$E$122,"=1000"))

and then ranked this dummy column to get my actual ranking
=RANK(K2,$K$2:$K$122,1)

I used help from this page by chris Pearson....
http://www.cpearson.com/excel/rank.htm

Thanks for all those who replied to this and other similar posts.

Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533404

Thanks for the reply Tom. I agree dummy column is certainly easier.....
dummy (me) didn't even think about a dummy column..huh. I will try you
solution also but may be not right away.

Ja

--
sa0200
-----------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=53340

So, I am using this formula to creat a dummy column and then rank tha
dummy column to get my ranking correctly.

=IF(F2=1000,RANK(F2,$F$2:$F$122,0)+RANK(J2,$J1:$J $122,0)/100,RANK(J2,$J$2:$J$122,0)+RANK(F2,$F$2:$F$122,0)/1000+COUNTIF($F$2:$F$122,"=1000"))

Now my problem is, my no. of rows changes very frequently...is there
way to change the no. of rows automatically. I wouldn't mind a macro i
thats what it takes. No. of rows that varies here is 122 in abov
formula.
I have column B that I can may be use to get the no. of rows wit
data......

Thanks for help. Ja

--
sa0200
-----------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=53340

Insert Name Define

Name: RngF
Refersto: =Offset(Sheet1!$F$1,1,0,CountA(Sheet1!$F:$F$)-1,1)

Add button

Name: RngJ
Refersto: =Offset(Sheet1!$F$1,1,4,CountA(Sheet1!$F:$F$)-1,1)
Add Button

then replace rngF for $F$2:$F$122 and rngJ for $J$2:$J$122


--
Regards,
Tom Ogilvy

"sa02000" wrote in
message ...

So, I am using this formula to creat a dummy column and then rank that
dummy column to get my ranking correctly.


=IF(F2=1000,RANK(F2,$F$2:$F$122,0)+RANK(J2,$J1:$J $122,0)/100,RANK(J2,$J$2:$
J$122,0)+RANK(F2,$F$2:$F$122,0)/1000+COUNTIF($F$2:$F$122,"=1000"))

Now my problem is, my no. of rows changes very frequently...is there a
way to change the no. of rows automatically. I wouldn't mind a macro if
thats what it takes. No. of rows that varies here is 122 in above
formula.
I have column B that I can may be use to get the no. of rows with
data......

Thanks for help. Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile:
http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533404

Tom, There couldn't be a better solution than this. Its working great
for me. Thank you so much.

Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533404