Gfrktyl

I have a row of 6 alpha-numeric values as in the picture. I need a formula that will determine the three highest numerical values across each row, and then display the letters associated with those values in the correct order (descending). For example, row 1 results in an answer RES, as R is the highest in the row, followed by E, followed by S. Where there is is a match (as in above), the first to appear gets preference. I am a basic user of Excel and this has got me stumped. I can do elements of the solution but it doesnt like it when I try to combine. Grateful for your help.

How it works:

My Source Data are in Range A2:F3.

• 
  

  Write this Formula in Cell B6 to split alphabets from Source Data & fill Right.

  
  
  

    =LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
  
  

  
  


• 
  

  To split Numbers from Source Data enter this Formula in Cell B7, fill Right then Down.

  
  
  

   =VALUE(RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1))
  
  

  
  


• 
  

  In Cell B10 write this Array Formula, fill Right then Press F2 & finish with Ctrl+Shift+Enter and fill Down.

  
  
  

  {=LARGE(B7:G7,{1,2,3})}
  
  

  
  


• 
  

  Write this Formula in Cell B13 fill Right then Down.

  
  
  

     =IFERROR(INDEX(B$6:$G$6,MATCH(B10,B7:$G7,0)),"")
  
  

  
  



• Finally, in Cell B16 write this Formula & fill down.

=CONCATENATE(B13,C13,D13)

Adjust cell deferences in Formula as needed.

Assuming that your data 'R35' is located at A1.

do

H1  --->  =VALUE(RIGHT(A1,LEN(A1)-1))

and drag until L1, then

N1  --->  =IF(COUNTIF($H1:$L1,H1)=1,H1,H1+0.5)

O1  --->  =IF(COUNTIF($H1:$L1,I1)=1,I1,I1+0.4)

P1  --->  =IF(COUNTIF($H1:$L1,J1)=1,J1,J1+0.3)

Q1  --->  =IF(COUNTIF($H1:$L1,K1)=1,K1,K1+0.2)

R1  --->  =IF(COUNTIF($H1:$L1,L1)=1,L1,L1+0.1)

then

T1  --->  =RANK(N1,$N1:$R1,0)

and drag until X1, then

Z1  --->  =INDEX($A1:$F1,MATCH(1,$T1:$X1,0))

AA1  --->  =INDEX($A1:$F1,MATCH(2,$T1:$X1,0))

AB1  --->  =INDEX($A1:$F1,MATCH(3,$T1:$X1,0))

then

AD1  --->  =LEFT(Z1)&LEFT(AA1)&LEFT(AB1)

lastly.. select H1:AD1 and drag until AD6.

AD column should be what your are looking for. You may hide the columns or do it in another sheet to make it look simpler.

Please share if you get stuck ( in understanding the formula or doing it ). ( :

hope it helps.

p/s : the +0.5 , +0.4 .. +0.1 is used to cater this requirement

the first to appear gets preference

I needed 6 helper cells without using VBS. So if your data is in A1 through F1:

set G1 to

=INT(RIGHT(A1,2)&"006")

set H1 to

=INT(RIGHT(B1,2)&"005")

set I1 to

=INT(RIGHT(C1,2)&"004")

set J1 to

=INT(RIGHT(D1,2)&"003")

set K1 to

=INT(RIGHT(E1,2)&"002")

set L1 to

=INT(RIGHT(F1,2)&"001")

and M1 to

=LEFT(INDIRECT(ADDRESS(ROW(),MATCH(LARGE(G1:L1,1),G1:L1,0))),1)&LEFT(INDIRECT(ADDRESS(ROW(),MATCH(LARGE(G1:L1,2),G1:L1,0))),1)&LEFT(INDIRECT(ADDRESS(ROW(),MATCH(LARGE(G1:L1,3),G1:L1,0))),1)

You should be able to copy and paste those 7 formulas down your rows.
Note that duplicate values are handled from left to right.