Excel - horizontal consecutive ranking in row

S

SamJakob

New Member
Joined
Jun 23, 2017
Messages
5
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]1st
[/TD]
[TD]H[/TD]
[TD]Result
[/TD]
[TD]2nd
[/TD]
[TD]H
[/TD]
[TD]Result[/TD]
[TD]3rd[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD]4th[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD]5th[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]<<irrelevant>>[/TD]
[TD]D[/TD]
[TD]12.09[/TD]
[TD]<<irrelevant>>[/TD]
[TD]E[/TD]
[TD]12.09[/TD]
[TD]<<irrelevant>>[/TD]
[TD]C[/TD]
[TD]12.10[/TD]
[TD]<<irrelevant>>[/TD]
[TD]B[/TD]
[TD]12.10[/TD]
[TD]<<irrelevant>>[/TD]
[TD]A[/TD]
[TD]12.12[/TD]
[/TR]
</tbody>[/TABLE]

So I have the above table and then I want a consecutive rank for each 'H' value.

For example, at the moment I have

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B
[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

What I want is

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]



So far I've seen methods of doing this vertically instead of horizontally but I can't manage to adapt them.

Help would be much appreciated.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Welcome to the forum.

I found your description to be a bit confusing. Do you mean that you want a formula in the red cells below, with these values?

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]1st[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD]2nd[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD]3rd[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD]4th[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD]5th[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]<<irrelevant>></irrelevant>[/TD]
[TD]1[/TD]
[TD]12.09[/TD]
[TD]<<irrelevant>></irrelevant>[/TD]
[TD]1[/TD]
[TD]12.09[/TD]
[TD]<<irrelevant>></irrelevant>[/TD]
[TD]2[/TD]
[TD]12.10[/TD]
[TD]<<irrelevant>></irrelevant>[/TD]
[TD]2[/TD]
[TD]12.10[/TD]
[TD]<<irrelevant>></irrelevant>[/TD]
[TD]3[/TD]
[TD]12.12[/TD]
[/TR]
</tbody>[/TABLE]

And you want the formula to be a ranking based on the values in the Result cells, without gaps? (The numbers in the table don't match your specified desired results. If I'm wrong, please explain how you get those values.)

If that is correct, how do you feel about rearranging your data? Most formulas of this sort use COUNTIF over the range somehow, and having the data and the results in the same range would result in a circular reference. If you want to keep it in this arrangement, I'd suggest putting the data values in a helper table somewhere, calculate the results there, then use some kind of LOOKUP to put in the H column.

Let me know if I understand your requirements correctly.
 
Upvote 0
Desired table:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1st
[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD]2nd[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD]3rd[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD]4th[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD]5th[/TD]
[TD]H[/TD]
[TD]Result[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]<<unrequired>>[/TD]
[TD]D[/TD]
[TD]12.09[/TD]
[TD]<<unrequired>>[/TD]
[TD]E[/TD]
[TD]12.09[/TD]
[TD]<<unrequired>>[/TD]
[TD]C[/TD]
[TD]12.10[/TD]
[TD]<<unrequired>>[/TD]
[TD]B[/TD]
[TD]12.10[/TD]
[TD]<<unrequired>>[/TD]
[TD]A[/TD]
[TD]12.12[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Also, sorry about the desired results I put in the first post, it seems I've confused myself there, it should have been

D - 1
E - 1
B - 2
C - 2
A - 3

(as shown in the Desired table)
 
Upvote 0
Try this:

Excel 2012
ABCDEFGHIJKLMNOPQRSTU

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]1st[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]Result[/TD]
[TD="bgcolor: #FFFFFF"]2nd[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]Result[/TD]
[TD="bgcolor: #FFFFFF"]3rd[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]Result[/TD]
[TD="bgcolor: #FFFFFF"]4th[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]Result[/TD]
[TD="bgcolor: #FFFFFF"]5th[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]Result[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]B[/TD]
[TD="bgcolor: #FFFFFF"]C[/TD]
[TD="bgcolor: #FFFFFF"]D[/TD]
[TD="bgcolor: #FFFFFF"]E[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]<>[/TD]
[TD="bgcolor: #FFFFFF"]D[/TD]
[TD="bgcolor: #FFFFFF"]12.09[/TD]
[TD="bgcolor: #FFFFFF"]<>[/TD]
[TD="bgcolor: #FFFFFF"]E[/TD]
[TD="bgcolor: #FFFFFF"]12.09[/TD]
[TD="bgcolor: #FFFFFF"]<>[/TD]
[TD="bgcolor: #FFFFFF"]C[/TD]
[TD="bgcolor: #FFFFFF"]12.1[/TD]
[TD="bgcolor: #FFFFFF"]<>[/TD]
[TD="bgcolor: #FFFFFF"]B[/TD]
[TD="bgcolor: #FFFFFF"]12.1[/TD]
[TD="bgcolor: #FFFFFF"]<>[/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]12.12[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"]3[/TD]
[TD="bgcolor: #FFFFFF"]2[/TD]
[TD="bgcolor: #FFFFFF"]2[/TD]
[TD="bgcolor: #FFFFFF"]1[/TD]
[TD="bgcolor: #FFFFFF"]1[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]Q2[/TH]
[TD="align: left"]{=SUM(IF(ISNUMBER($A$2:$O$2),IF($A$2:$O$2<INDEX($A$2:$O$2,MATCH(Q1,$A$2:$O$2,0)+1),1/COUNTIF($A$2:$O$2,$A$2:$O$2))))+1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I'm a little puzzled why B2 is D and E2 is E, but it doesn't matter. This formula checks the header in Q1:U1 for the letter, and ranks the value it finds after that letter.

Let me know if this works for you.
 
Upvote 0
It is not working for me.

The formula appears to have a syntax error in it:
2),IF($A$2:$O$2[should there be something here?]<font color="green" style="background-color: rgb(250, 250, 250);"><index(<font color="purple">$A$2:$O$2,MATC</index(
 
Upvote 0
Arrgh! Even after all this time, I sometimes forget that the bulletin board software here can interpret a < as an HTML tag instead of part of a formula. To avoid that, one should put a space after it. See if this works:

Excel 2012
ABCDEFGHIJKLMNOPQRSTU

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]1st[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]Result[/TD]
[TD="bgcolor: #FFFFFF"]2nd[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]Result[/TD]
[TD="bgcolor: #FFFFFF"]3rd[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]Result[/TD]
[TD="bgcolor: #FFFFFF"]4th[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]Result[/TD]
[TD="bgcolor: #FFFFFF"]5th[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]Result[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]B[/TD]
[TD="bgcolor: #FFFFFF"]C[/TD]
[TD="bgcolor: #FFFFFF"]D[/TD]
[TD="bgcolor: #FFFFFF"]E[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]<>[/TD]
[TD="bgcolor: #FFFFFF"]D[/TD]
[TD="bgcolor: #FFFFFF"]12.09[/TD]
[TD="bgcolor: #FFFFFF"]<>[/TD]
[TD="bgcolor: #FFFFFF"]E[/TD]
[TD="bgcolor: #FFFFFF"]12.09[/TD]
[TD="bgcolor: #FFFFFF"]<>[/TD]
[TD="bgcolor: #FFFFFF"]C[/TD]
[TD="bgcolor: #FFFFFF"]12.1[/TD]
[TD="bgcolor: #FFFFFF"]<>[/TD]
[TD="bgcolor: #FFFFFF"]B[/TD]
[TD="bgcolor: #FFFFFF"]12.1[/TD]
[TD="bgcolor: #FFFFFF"]<>[/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]12.12[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"]3[/TD]
[TD="bgcolor: #FFFFFF"]2[/TD]
[TD="bgcolor: #FFFFFF"]2[/TD]
[TD="bgcolor: #FFFFFF"]1[/TD]
[TD="bgcolor: #FFFFFF"]1[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]Q2[/TH]
[TD="align: left"]{=SUM(IF(ISNUMBER($A$2:$O$2),IF($A$2:$O$2< INDEX($A$2:$O$2,MATCH(Q1,$A$2:$O$2,0)+1),1/COUNTIF($A$2:$O$2,$A$2:$O$2))))+1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Sum of Lowest Rankings
2
Replies
10
Views
335
Fluff
Fluff
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
100
montecarlo2012
montecarlo2012
W
  • Question Question
How to copy paste date wise horizontal employee attendance vertically
Replies
4
Views
226
WasimK
W
C
  • Question Question
Search/Find specific word or match in multiple columns and highlight the cells that do not match
Replies
1
Views
37
chueeee
C
JenniferMurphy
  • Question Question
Why does this SumProduct get a Value error?
Replies
4
Views
71
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top