# Rank within different segments

#### alizok

##### Board Regular
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

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

#### fairwinds

##### MrExcel MVP
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
thank you very much. This is the formula that i was looking for. ray:

#### alizok

##### Board Regular
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.

#### fairwinds

##### MrExcel MVP

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

it doesn't work

#### fairwinds

##### MrExcel MVP

Be specific.

What does not work, what result do you get?

Did you remember Ctrl + Shift + Enter ?

#### alizok

##### Board Regular
sorry about it I get this #NAME? and i did enter it with ctr+shift+enter

#### alizok

##### Board Regular
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

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.

Replies
7
Views
85
Replies
6
Views
300
Replies
6
Views
230
Replies
8
Views
2K
Replies
1
Views
535

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

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.

### Which adblocker are you using?

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

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