Sort Columns

[Saxman]

I have two columns of figures. My objective is to select the highest figure in
adjacent cells from the two columns and award points 25, 20, 15, 10, 5 to the top
five in the remaining column. What is the best way to tackle this? Example below.

Column A Column B
53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

Best of both columns.
53
82
71
72
68
63
52
63

The above sorted.
82 25pts.
72 20
71 15
68 10
63 5
63
53
52

As one can see above where pts. equal 63 one could rely on the sort, award both 5
pts. or split the pts. between both (or more). I'm not too bothered about this
aspect though.



--

 

[Guest]

Hi Saxman,

A couple of stages.

First in column C extract the largest from the 2 columns with the formula

=MAX(A$1,B$1) drag this down for the length of you data.

Now make a table with your ranking and points

1 25
2 20
3 15
4 10
5 5

In my example it's in I1 to J5

Now in D1 enter the formula below and drag down and it will award the points.

=IF(ISERROR(VLOOKUP(RANK(C1,C$1:C$8),I$1:J$5,2,FALSE)),"",VLOOKUP(RANK(C1,C$1:C$8),I$1:J$5,2,FALSE))


Mike

 

[Guest]

Saxman,

I'm having an elderly moment, you can do it in 1 formula, not two.

Create your table and put this in c1 and drag down.

=IF(ISERROR(VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I$1:J$5,2,FALSE)),"",VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I$1:J$5,2,FALSE))

Mike

 

[Guest]

Circular reference ????

Saxman,

I'm having an elderly moment, you can do it in 1 formula, not two.

Create your table and put this in c1 and drag down.

=IF(ISERROR(VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I$1:J$5,2,FALSE)),"",VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I$1:J$5,2,FALSE))

Mike

 

[Guest]

Well a bit ovalish""

Go back to the first option

Thanls Toppers.


Mike

 

[Saxman]

I'm having an elderly moment, you can do it in 1 formula, not two.

Create your table and put this in c1 and drag down.

=IF(ISERROR(VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I$1:J$5,2,FALSE)),"",VLOOKUP(RANK(MAX(
A1:B1),C$1:C$8),I$1:J$5,2,FALSE))

Thanks Mike, but I get a circular reference if I paste in cell C1?

 

[Saxman]

A couple of stages.

First in column C extract the largest from the 2 columns with the formula

=MAX(A$1,B$1) drag this down for the length of you data.

Now make a table with your ranking and points

1 25
2 20
3 15
4 10
5 5

In my example it's in I1 to J5

Now in D1 enter the formula below and drag down and it will award the points.

=IF(ISERROR(VLOOKUP(RANK(C1,C$1:C$8),I$1:J$5,2,FALSE)),"",VLOOKUP(RANK(C1,C$1:C$8),
I$1:J$5,2,FALSE))

This works fine.

--

 

[Don Guillett]

try this.Copy down
=LARGE(a:b,ROW(A1))
you may like this for the reward. copy down
=30-ROW(A1)*5

 

[Saxman]

try this.Copy down
=LARGE(a:b,ROW(A1))

This finds the best of both columns. I need to find the best between adjacent cells
first, then sort.

you may like this for the reward. copy down
=30-ROW(A1)*5

This works fine.




--

 

[Don Guillett]

I think this array formula (entered with ctrl+shift+enter) will do it .
Change j to a and k to b.

=LARGE(IF($J$2:$J$6>$K$2:$K$6,$J$2:$J$6,$K$2:$K$6),ROW(A1))

 

[Saxman]

I think this array formula (entered with ctrl+shift+enter) will do it .
Change j to a and k to b.

=LARGE(IF($J$2:$J$6>$K$2:$K$6,$J$2:$J$6,$K$2:$K$6),ROW(A1))

=LARGE(IF($A$1:$A$8>$B$1:$B$8,$A$1:$A$8,$B$1:$B$8),ROW(A1))

It doesn't appear to select the larger number from both columns.

53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

The above give the following result below.

74
72
71
68
63
63
52
42

It should be.

82
72
71
68
63
63
53
52

--

 

[Don Guillett]

Did you array enter as instructed?

 

[Gord Dibben]

Don's formula entered in C1 and copied down to C8 gives me the result you want.

82
72
71
68
63
63
53
52

Did you remember to CTRL + SHIFT + ENTER when placing the formula in C1?

If not, you'll get the results you did get.


Gord Dibben MS Excel MVP

 

[Saxman]

Did you array enter as instructed?

I still get the same results.

Do I paste the formula in C1 and then CTRL + SHIFT + ENTER?


Maybe I'm doing something wrong?

--

 

[Saxman]

Did you array enter as instructed?

I entered it manually, did as you said and all is fine now.

Thanks a million!

--

 

[Don Guillett]

Glad to help