Using functions to rank and sort catagories

[eefs]

This seems like something that should be easy to do but.......
I want excel to sort a field using functions, so that it's done automatically with needing anyone to manually mess with it. However everything falls apart when two or more fields have the same value e.g. when I have 3 items in 6th place. Is there another function that I could use to get excel to use the next field to sort those items that have the same value and then return to the first field to continue again. I'm basically trying to emulate the Data;Sort function, where the sort order can be specified. Any help would be appreciated

[Chris Davison]

Microsoft Excel - eefs.xls_______________Running: xl97 : OS = Windows (32-bit) 4.90
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
H5		=
*	A	B	C	D	E	F
1	Name	Amount	Rank	Count	Count-1	Rank&Count
2	John	123	:alert('=RANK(B2,$B$2:$B$9)')>8	:alert('=COUNTIF($B$2:B2,B2)')>1	:alert('=COUNTIF($B$2:B2,B2)-1')>0	:alert('=RANK(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1')>8
3	Paul	234	:alert('=RANK(B3,$B$2:$B$9)')>7	:alert('=COUNTIF($B$2:B3,B3)')>1	:alert('=COUNTIF($B$2:B3,B3)-1')>0	:alert('=RANK(B3,$B$2:$B$9)+COUNTIF($B$2:B3,B3)-1')>7
4	George	777	:alert('=RANK(B4,$B$2:$B$9)')>2	:alert('=COUNTIF($B$2:B4,B4)')>1	:alert('=COUNTIF($B$2:B4,B4)-1')>0	:alert('=RANK(B4,$B$2:$B$9)+COUNTIF($B$2:B4,B4)-1')>2
5	Ringo	777	:alert('=RANK(B5,$B$2:$B$9)')>2	:alert('=COUNTIF($B$2:B5,B5)')>2	:alert('=COUNTIF($B$2:B5,B5)-1')>1	:alert('=RANK(B5,$B$2:$B$9)+COUNTIF($B$2:B5,B5)-1')>3
6	Harry	900	:alert('=RANK(B6,$B$2:$B$9)')>1	:alert('=COUNTIF($B$2:B6,B6)')>1	:alert('=COUNTIF($B$2:B6,B6)-1')>0	:alert('=RANK(B6,$B$2:$B$9)+COUNTIF($B$2:B6,B6)-1')>1
7	Sally	345	:alert('=RANK(B7,$B$2:$B$9)')>6	:alert('=COUNTIF($B$2:B7,B7)')>1	:alert('=COUNTIF($B$2:B7,B7)-1')>0	:alert('=RANK(B7,$B$2:$B$9)+COUNTIF($B$2:B7,B7)-1')>6
8	Tom	456	:alert('=RANK(B8,$B$2:$B$9)')>5	:alert('=COUNTIF($B$2:B8,B8)')>1	:alert('=COUNTIF($B$2:B8,B8)-1')>0	:alert('=RANK(B8,$B$2:$B$9)+COUNTIF($B$2:B8,B8)-1')>5
9	Dick	567	:alert('=RANK(B9,$B$2:$B$9)')>4	:alert('=COUNTIF($B$2:B9,B9)')>1	:alert('=COUNTIF($B$2:B9,B9)-1')>0	:alert('=RANK(B9,$B$2:$B$9)+COUNTIF($B$2:B9,B9)-1')>4
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.20]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Does this help ?

Using the normal rank function, you then add a count using an increasing row range less 1

the formulae in each column left to right show the logic step by step... just click on the numbers if they're underlined

[shades]

Chris,

Although I didn't ask this question, it does lead into a project I have. My problem with the rank is that I have 17 cities down the sides, and six companies across the top. But for each company I have three columns of data. Thus, to rank each one, I would need to select, for example, E3, H3, K3, N3, Q3, T3. It doesn't seem that rank works in non-continguous files. Is there any way around this?

[Chris Davison]

Shades,

rather than use non-contiguous ranges in the formula, replace them with a named range that refers to a range of the non-contiguous cells :

Microsoft Excel - shades rank non contig.xls_______________Running: xl97 : OS = Windows (32-bit) 4.90

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

F21

=

*

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Location

*

*

*

Company1

Rank

*

Company2

Rank

*

Company3

Rank

*

2

City1

*

*

*

123

:alert('=RANK(E2,company)')>45

*

128

:alert('=RANK(H2,company)')>43

*

83

:alert('=RANK(K2,company)')>49

*

3

City2

*

*

*

234

:alert('=RANK(E3,company)')>38

*

239

:alert('=RANK(H3,company)')>37

*

194

:alert('=RANK(K3,company)')>41

*

4

City3

*

*

*

345

:alert('=RANK(E4,company)')>27

*

350

:alert('=RANK(H4,company)')>25

*

305

:alert('=RANK(K4,company)')>33

*

5

City4

*

*

*

456

:alert('=RANK(E5,company)')>20

*

461

:alert('=RANK(H5,company)')>19

*

416

:alert('=RANK(K5,company)')>23

*

6

City5

*

*

*

567

:alert('=RANK(E6,company)')>15

*

572

:alert('=RANK(H6,company)')>13

*

527

:alert('=RANK(K6,company)')>17

*

7

City6

*

*

*

678

:alert('=RANK(E7,company)')>9

*

683

:alert('=RANK(H7,company)')>7

*

638

:alert('=RANK(K7,company)')>11

*

8

City7

*

*

*

9999

:alert('=RANK(E8,company)')>2

*

10004

:alert('=RANK(H8,company)')>1

*

9959

:alert('=RANK(K8,company)')>3

*

9

City8

*

*

*

344

:alert('=RANK(E9,company)')>28

*

349

:alert('=RANK(H9,company)')>26

*

304

:alert('=RANK(K9,company)')>34

*

10

City9

*

*

*

566

:alert('=RANK(E10,company)')>16

*

571

:alert('=RANK(H10,company)')>14

*

526

:alert('=RANK(K10,company)')>18

*

11

City10

*

*

*

677

:alert('=RANK(E11,company)')>10

*

682

:alert('=RANK(H11,company)')>8

*

637

:alert('=RANK(K11,company)')>12

*

12

City11

*

*

*

788

:alert('=RANK(E12,company)')>5

*

793

:alert('=RANK(H12,company)')>4

*

748

:alert('=RANK(K12,company)')>6

*

13

City12

*

*

*

112

:alert('=RANK(E13,company)')>48

*

117

:alert('=RANK(H13,company)')>47

*

72

:alert('=RANK(K13,company)')>51

*

14

City13

*

*

*

223

:alert('=RANK(E14,company)')>40

*

228

:alert('=RANK(H14,company)')>39

*

183

:alert('=RANK(K14,company)')>42

*

15

City14

*

*

*

334

:alert('=RANK(E15,company)')>31

*

339

:alert('=RANK(H15,company)')>29

*

294

:alert('=RANK(K15,company)')>35

*

16

City15

*

*

*

443

:alert('=RANK(E16,company)')>22

*

448

:alert('=RANK(H16,company)')>21

*

403

:alert('=RANK(K16,company)')>24

*

17

City16

*

*

*

332

:alert('=RANK(E17,company)')>32

*

337

:alert('=RANK(H17,company)')>30

*

292

:alert('=RANK(K17,company)')>36

*

18

City17

*

*

*

119

:alert('=RANK(E18,company)')>46

*

124

:alert('=RANK(H18,company)')>44

*

79

:alert('=RANK(K18,company)')>50

*

19

*

*

*

*

*

*

*

*

*

*

*

*

*

20

*

*

*

*

*

= named range "company"

*

*

*

*

*

*

*

Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.20]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


However, not sure how you'll get round duplicates in this case as countif certainly doesn't seem to like a named range.... I'll have a think on that one, but the basic answer is yes, use named ranges instead for basic ranking

[shades]

Thanks, Chris.

However, the ranking is always between 1 and 6. And the ranking repeats for every city. Like this:

Company RRR SSS TTT XXX YYY ZZZ
Dover 1 3 5 6 4 2
Nairobi 3 5 6 2 1 4

For each city, each company has a certain value (i.e. sales). Those six companies then are to be ranked within that city. Each month those sales figures change.

(As an added issue, I need to set the cell color based on that ranking. And this changes each month.)

Altogether I have 41 cities in two separate worksheets - but always the same six companies (requirements that I cannot change).

Hope this explains a little better.

[Chris Davison]

On 2002-05-15 13:55, shades wrote:
Thanks, Chris.

However, the ranking is always between 1 and 6. And the ranking repeats for every city. Like this:

Company RRR SSS TTT XXX YYY ZZZ
Dover 1 3 5 6 4 2
Nairobi 3 5 6 2 1 4

For each city, each company has a certain value (i.e. sales). Those six companies then are to be ranked within that city. Each month those sales figures change.

(As an added issue, I need to set the cell color based on that ranking. And this changes each month.)

Altogether I have 41 cities in two separate worksheets - but always the same six companies (requirements that I cannot change).

Hope this explains a little better.

Isn't it still the same ? just name the ranges.

Or, somewhere safe and unused, reference the data that you want ranked with simple linked cells into a range that is contiguous and just perform the rankings on that range


Your 6 colours are going to need a VBA solution... there's 3 max via conditional formatting

[shades]

I had thought about the separate file just for doing the ranking. That may be the easiest solution.

As for the six colors, after investigating a little over the past month, I knew that VBA was the only option. I now have a book on VBA that I am beginning. So, perhaps in a few weeks I will have the code I need.

Thanks again for the help.

[Chris Davison]

if you get stuck, there's a few examples already written - off the top of my head try a search on this site for entries by Ivan with conditional format as the keywords

Good luck with the VBA book, I think I probably have the same one sitting on my desk here shouting at me !

[shades]

Just to let you know, the rank function worked great. Now I have it as part of a working template for every month. Saves even more time. Thanks!