Ranking based on values from two Columns

[abhi2611]

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!

 

[vletm]

abhi2611
Without Excel-file
... isn't this basic Sorting -case with both sides?

 

[Peter Bartholomew]

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})

 

[abhi2611]

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.

 

[abhi2611]

abhi2611
Without Excel-file
... isn't this basic Sorting -case with both sides?

Hi Vletm,

Yes it is but the thing is the raw data is generated by sql database and is directly sent to the end user.

 

[vletm]

abhi2611
The things are that You've written something
as well as You've skipped to upload a sample file,
which should be something same as You are using.

 

[Peter Bartholomew]

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.