0

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.

enter image description here

3
  • 1
    What elements do you have?
    – Forward Ed
    Dec 5, 2018 at 13:43
  • 1
    is it always just 2 digitsat the end?
    – Forward Ed
    Dec 5, 2018 at 13:46
  • 6 elements R, I, A, S, E, C and always just two digits at the end.
    – Joel
    Dec 6, 2018 at 15:29

3 Answers 3

0

enter image description here

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.

3
  • Thanks for your help, but again I cant get this to work. In step three, do I need to make all of those key presses at once or one after the other?
    – Joel
    Dec 6, 2018 at 15:36
  • @Joel,, Step 1 need to be executed once only since Alphabets are common in all rows. Step 2 need to fill down for all rows so that Step 4 & 5 also. I've taken few rows for better understanding,, you need to follow the show steps and will works. The Screen Shot is part of the active Sheet,, I've work around and posted finally. Dec 7, 2018 at 4:55
  • Cont,, read Step 3 carefully,, I've written how to execute it,, being as Answerer I've done all for you,, now it's your turn to apply yourself. I'm unable to understand that Y U r facing problem!! Dec 7, 2018 at 4:58
0

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

2
  • Many thanks for your solution, but I can't get it to work. It seems to go ok until "and drag until AB1, then" as when I do, it gives me R35 in Z, AA and AB columns. I think the correct answer is R35 in Z, E35 in AA and S29 in AB. Can you tell me where I'm going wrong? I transposed the data starting at A1 so I did not get any referencing errors.
    – Joel
    Dec 6, 2018 at 15:34
  • you are right.. I forgot that Z, AA, and AB is not exactly the same formula. Ans edited.
    – p._phidot_
    Dec 6, 2018 at 15:38
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .