Forum Discussion
How to make a function to generate genetic code table?
@ChocolateTibi BTW, if the other person's prior solution works for you great, but my excel doesn't like SORT or SEQUENCE. Also, just for fun I thought of another way. This way is a little more 'generic' in that it doesn't explicitly require "R" first and "B" second but still requires first and second char different:
=CHAR(MIN(CODE($A7),CODE(B$6)))&CHAR(MAX(CODE($A7),CODE(B$6)))&CHAR(MIN(CODE(RIGHT($A7,1)),CODE(RIGHT(B$6,1))))&CHAR(MAX(CODE(RIGHT($A7,1)),CODE(RIGHT(B$6,1))))
if you want to combine things like "RR" & "rr" to get "RRrr" the above would have to get expanded to use LARGE and include all 4 characters in each case.
ChocolateTibi Since you have first char joined with first char and second char joined with second char and only 1 pair order that is 'incorrect' I proposed the following:
=CONCAT(IF(CONCAT(LEFT($A2,1),LEFT(B$1,1))="rR","Rr",CONCAT(LEFT($A2,1),LEFT(B$1,1))),IF(CONCAT(RIGHT($A2,1),RIGHT(B$1,1))="bB","Bb",CONCAT(RIGHT($A2,1),RIGHT(B$1,1))))
to break it down it combines first char of each and then second char of each. In each case it checks if the order is wrong (i.e. "RR", "Rr", and "rr" are all OK, only "rR" is backwards) then reverse it:
=CONCAT(
IF(CONCAT(LEFT($A2,1),LEFT(B$1,1))="rR",
"Rr",
CONCAT(LEFT($A2,1),LEFT(B$1,1))),
IF(CONCAT(RIGHT($A2,1),RIGHT(B$1,1))="bB",
"Bb",
CONCAT(RIGHT($A2,1),RIGHT(B$1,1))))
@ChocolateTibi BTW, if the other person's prior solution works for you great, but my excel doesn't like SORT or SEQUENCE. Also, just for fun I thought of another way. This way is a little more 'generic' in that it doesn't explicitly require "R" first and "B" second but still requires first and second char different:
=CHAR(MIN(CODE($A7),CODE(B$6)))&CHAR(MAX(CODE($A7),CODE(B$6)))&CHAR(MIN(CODE(RIGHT($A7,1)),CODE(RIGHT(B$6,1))))&CHAR(MAX(CODE(RIGHT($A7,1)),CODE(RIGHT(B$6,1))))
if you want to combine things like "RR" & "rr" to get "RRrr" the above would have to get expanded to use LARGE and include all 4 characters in each case.
- mtarler It's working, and easy to understand. Thank for your contribution! I learned a lot with your solutions.
ChocolateTibiYou're very welcome, I'm glad I could help, and most importantly I'm glad to hear you learned more.
