Forum Discussion
How to make a function to generate genetic code table?
I would like to make a function which generates the green columns with this character sort.
For example, A2 cell is RB and B1 cell is rB the result will be: RrBB (first concatenate the two cell's string, than sort the characters as: R<r<B<b).
Could I make this in excel?
gen_code
I could very appreciate your help, I'm new in excel.
Tibi
@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.
6 Replies
Clearly this is no use to anyone at present but it may still be of some interest in the longer term. The LET function allows a sequence of local names to be generated and used within the formula
= LET(
joined, @string1&@string2,
split, MID(joined, {1,2,3,4}, 1),
case, BITAND(CODE(split),32)>0,
sorted, SORTBY(split, split,-1, case,1),
CONCAT(sorted) )joined forms a 4 character string from the row header and the column header
split is the same 4 characters as an array
case is a Boolean, TRUE for lower case (works by testing a specific bit in the ASCII code)
sorted uses reverse alphabetic order, followed by sorting by case.
Much of this can be done in traditional Excel by using defined names but clearly the SORTBY function is a new dynamic array function and the LET brings the definition of names out of Name Manager.
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.
hi ChocolateTibi,
You can trying using the below formula
=TEXTJOIN("",TRUE,SORT(MID((B$1&$A2),SEQUENCE(LEN(B$1&$A2),,1,1),1),,-1))Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more
