• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Ranking based on values from two Columns

abhi2611

Member
Hi,

Below is the output i get from a data base.

A
1​
B
2​
C
1​
D
1​
A
2​
B
1​
C
3​
D
4​
B
6​
C
5​
D
3​
A
3​
A
4​
B
3​
C
2​
D
2​

Is there a way I could automatically rank them as show below. This output will be in a sheet and I will have the Alphabets static but I want the numbers to automatically update based on their rank


A
1​
A
2​
A
3​
A
4​
B
1​
B
2​
B
3​
B
6​
C
1​
C
2​
C
3​
C
5​
D
1​
D
2​
D
3​
D
4​

Any help is appreciated.

Thanks!
 

Peter Bartholomew

Well-Known Member
The simplest solution might be a manually run sort.

Using functions, an Office 365 dynamic array solution would be
= SORT( data, {1,2} )

A traditional Excel solution could be achieved by adding a helper column with ranks
= 1 + COUNTIFS(character, "<" & character) + COUNTIFS(character, character, digit,"<" & digit)
and then looking up each output sequence number 'k' in the ranking column
= INDEX( data, MATCH( k, rank, 0 ), {1,2})
 

Attachments

abhi2611

Member
The simplest solution might be a manually run sort.

Using functions, an Office 365 dynamic array solution would be
= SORT( data, {1,2} )

A traditional Excel solution could be achieved by adding a helper column with ranks
= 1 + COUNTIFS(character, "<" & character) + COUNTIFS(character, character, digit,"<" & digit)
and then looking up each output sequence number 'k' in the ranking column
= INDEX( data, MATCH( k, rank, 0 ), {1,2})
Hi Peter,

Thank you for the solution. I should have been a little more specific but I am running into different scenarios.

1. How would this change if the columns are not next to each other?
2. If there are duplicate numbers in the numbers column, It needs to capture the first duplicate value and jump to the next number in the sequence.
2. There may be more rows added or deleted every week. Is there a way to adjust the formula to dynamically select the last row?

Thank you again for your input.
 
Last edited:

Peter Bartholomew

Well-Known Member
1. How would this change if the columns are not next to each other?
2. If there are duplicate numbers in the numbers column, It needs to capture the first duplicate value and jump to the next number in the sequence.
3. There may be more rows added or deleted every week. Is there a way to adjust the formula to dynamically select the last row?
1. The ranking does not rely on the columns being next to each other; they do not even need to be on the same sheet. The INDEX formula that returns the data in the specified order would need to be adjusted in order to return the required columns, e.g.
= INDEX( data, MATCH( k, rank, 0 ), {1,7})
or, by returning the columns using separate formulas
= INDEX( characters, MATCH( k, rank, 0 ) )
= INDEX( digits, MATCH( k, rank, 0 ) )
2.
I haven't considered filtering to remove duplicates. I would normally add a tie-breaker to the ranking and return all rows.
3. I would contend that database tables should be returned to an Excel Table and never to a directly referenced range. If you were to use Power Query for the database table lookup, that would allow you to complete any sorting and filtering before the data even appears in the workbook. If not, Excel tables resize to match the actual data so devices like entire column referencing A:A and G:G are no longer needed.
 
Top