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.
3 Answers
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 PressF2
& finish withCtrl+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.
-
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?– JoelDec 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
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
-
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.– JoelDec 6, 2018 at 15:34
-
you are right.. I forgot that Z, AA, and AB is not exactly the same formula. Ans edited. Dec 6, 2018 at 15:38
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.