Rank within different segments

alizok

Board Regular
Joined
Sep 12, 2002
Messages
76
Can someone help me on Ranking formula within different segments. for exaple i have 6 different segments that is in column A and than different numbers in column B i need to rank these numbers by segment. The segment are not sorted so it could be segment 1 in column A1 with number 32 in column B and then segment 5 in A2 with different number. Is there a way for me to rank nubers in colum B without sorting it by segment? Any suggestion is a great help.
thank you very much
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

You can try this formula that has to be entered with Ctrl + Shift + Enter
Book1.xls
ABCD
1SegmentNumberRank
2A14
3A23
4B33
5A42
6C51
7D61
8B72
9B81
10A91
Sheet4
 

alizok

Board Regular
Joined
Sep 12, 2002
Messages
76
thank you very much. This is the formula that i was looking for. :pray: :biggrin:
 

alizok

Board Regular
Joined
Sep 12, 2002
Messages
76
This formula works when i need to rank in descending order what about ascending? I try changing Large to samall but it didn't work. I also try changing the last Zero to 1 but i still don't get right output. Any suggestions would be great. :rolleyes:
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Like this?

=MATCH(B2,SMALL(IF($A$2:$A$100=A2,$B$2:$B$100,""),ROW(INDIRECT("1:100"))),0)

Entered with Ctrl + shift + enter in E2 and dragged down.
Book1
ABCDE
1SegmentNumberRankOpositerank
2A141
3A232
4B331
5A423
6C511
7D611
8B722
9B813
10A914
Sheet4
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Be specific.

What does not work, what result do you get?

Did you remember Ctrl + Shift + Enter ?
 

alizok

Board Regular
Joined
Sep 12, 2002
Messages
76
sorry about it I get this #NAME? and i did enter it with ctr+shift+enter :rolleyes:
 

alizok

Board Regular
Joined
Sep 12, 2002
Messages
76
i tried re-typing the entire formula again and now I'm getting #N/A for some output and for others i'm getting number. I'm totally confused? What am i doing wrong? why would it have an output in cell e3 but not in e4?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
I can't replicate your error.

Try applying the formula to a layout exactly as I posted and then change it to your needs successively. Or post a sample of your data, using the html maker, showing your data and the errors you get.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,679
Members
425,229
Latest member
Rashid mahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top