Rank a row, find column header WITH duplicate ranked values

[mrpaw]

HI all. I asked a similar question and was given an excellent reply that worked well except it doesn't work if there are duplicate values. If there are two duplicate values in a row the formula I'm using repeats the first column header for the second value. What I need is to find the second value and corresponding column header. I've been searching and it seems this is a recurring question with no clear resolution. Any ideas? Here is the formula I'm using, thank you Eric W, to find the ranked items and then the column header. =IFERROR(LARGE($B3:$O3,(COLUMNS($R3:R3)+1)/2),"") for the numeric values and the following array formula for the header lookup. {=IF(R3="","",IF(R3>0,INDEX($B$2:$O$2,SMALL(IF($B3:$O3=R3,COLUMN($B$3:$O$3)-COLUMN($B$3)+1),COUNTIF($P3:P3,R3)+1)),""))}

[TABLE="width: 500"]
<tbody>[TR]
[TD]Should be[/TD]
[TD]Rank[/TD]
[TD]Column Detail[/TD]
[TD]Ranked Value[/TD]
[/TR]
[TR]
[TD]Col D ("yellow")[/TD]
[TD]10[/TD]
[TD]Yellow[/TD]
[TD].50[/TD]
[/TR]
[TR]
[TD]Col H ("green")[/TD]
[TD]11[/TD]
[TD]Yellow[/TD]
[TD].50[/TD]
[/TR]
</tbody>[/TABLE]

The second .50 ranked value should show the column detail of GREEN for Col. H, not repeat Col. D.

See original question on this forum under: [h=1]Rank a row, then put values and column header in a table[/h]

 

[Eric W]

I'm confused by your question. The formula I provided handles duplicate values. If you look at the sample I posted in post 4 of the original thread, it shows duplicate values of 5 in R3 and T3, with different headings in Q3 and S3. The example you show in this thread doesn't show enough detail for me to see what you want. Try to post a more complete example.

 

[mrpaw]

I'm confused by your question. The formula I provided handles duplicate values. If you look at the sample I posted in post 4 of the original thread, it shows duplicate values of 5 in R3 and T3, with different headings in Q3 and S3. The example you show in this thread doesn't show enough detail for me to see what you want. Try to post a more complete example.

---

I've retried the formula and now it's not working. The range is larger so I changed all the range references but still getting duplicate column values on the second instance (duplicate). I must be missing a step. Yes, I am entering the formula as an array.

First Value of 1.00 from BT:

{=IF(BT7="","",IF(BT7>0,INDEX(TOTAL_Name,SMALL(IF($B7:$BI7=BT7,COLUMN($B7:$BI7)-COLUMN($B7)+1),COUNTIF($BL7:$BL7,BT7)+1)),""))}

Where Total_Name is the column header range. The first value in BT should show the column header from Col S


Duplicate value 1.00 from BV:

{=IF(BV7="","",IF(BV7>0,INDEX(TOTAL_Name,SMALL(IF($B7:$BI7=BV7,COLUMN($B7:$BI7)-COLUMN($B7)+1),COUNTIF($BL7:$BL7,BV7)+1)),""))}

The duplicate value in BV should show the column header from Col W

 

[mrpaw]

Found MY error. I needed to adjust to the last rank value cell to the left of the formula cell.

All is working now.