Ranking Pairs of Numbers in Separate Columns

[KennyT]

The database is columns 'A' and 'B'. Numbers in column 'A' are related to numbers in column 'B' and this relationship needs to remain when the numbers are reorganized in columns 'C' and 'D'. I would like to rank the pairs of numbers into two new columns ('C' and 'D') with the numbers in column 'D' establishing the ranking order from largest to smallest.

Columns 'A' and 'C' only have numbers 0 through 9 with no repeats. Columns 'B' and 'D' may have multiple repeats, and in some cases, five or more of the same number. Numbers in columns 'B' and 'D' could also be 0. However, where multiple repeats occur in column 'D', there can be no duplicates in column 'C'.

My efforts so far:

For cell C1, I used this formula:

=INDEX(A1:A10,MATCH(LARGE(B1:B10,1),B1:B10,0))

The LARGE formula value '1' changes to '2' for cell C2 and so on down the list. However, it only partially works because I get duplicate numbers in column 'C'.

For cell D1, I used this formula to set the ranking order from largest to smallest:

=LARGE(D1:D10,1)

The value '1' changes to '2' for cell D2 and so on down the list. This works, but independently from anything happening in column 'C'.

Any guidance would be most appreciated. Many thanks!

A B C D
------------------
0 1 8 12
1 8 7 9
2 3 1 8
3 4 6 7
4 4 5 6
5 6 9 6
6 7 3 4
7 9 4 4
8 12 2 3
9 6 0 1

 

[Marcelo Branco]

Formula in D1 copied down
=LARGE($B$1:$B$10,ROWS($D$1:D1))

Array formula in C1 copied down
=INDEX($A$1:$A$10,SMALL(IF($B$1:$B$10=D1,ROW($A$1:$A$10)-ROW($A$1)+1),COUNTIF($D$1:D1,D1)))

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

If you are not familiar with Array Formulas take a look at
Array Formulas

See inside Single Value Result Array Formulas
ENTERING AN ARRAY FORMULA

M.

 

[KennyT]

Thank you for your time in replying to my issue. I will have a look at your suggestion and try it in my spreadsheet to see how it works. It may be a few days before I have a chance to respond again, so I really appreciate your help.

 

[KennyT]

I tested the formulas in my spreadsheet and they work perfectly.

Thanks so much for your fast and professional assistance.

Cheers!

 

[Marcelo Branco]

I tested the formulas in my spreadsheet and they work perfectly.

Thanks so much for your fast and professional assistance.

Cheers!

You are welcome. Glad for helping

M.