Add "But if..." to an IF-formula

[Tinwelende]

I can't let go of this problem... It really feels like it should be possible to be solved by just adding something to the IF-formula.

In column R I have this code: =IF(C4="";"";IF(CODE(C4)>CODE(D4);D4;C4))

and in column S I have this code: =IF(D4="";"";IF(CODE(D4)>CODE(C4);D4;C4))

It works perfectly, except for when column C or D have Cd, cd, ce or Ce in them, since they differ (otherwise it's just A or a, P or p and so on, and for that, it works).

Cd or cd ALWAYS has to come last (be sorted into the S- and R-column).

SO.... is it possible to add this to the formulas above?

=IF(C4="";"";IF(CODE(C4)>CODE(D4);D4;C4)) BUT IF C4 contains Cd and D4 contains Ce, then R4 should say Ce, BUT IF C4 contains cd and D4 contains ce, then R4 should say ce, BUT IF C4 contains Ce and D4 contains Cd, then S4 should say Ce, BUT IF C4 contains cd and D4 contains Ce, then S4 should say Ce.

=IF(D4="";"";IF(CODE(D4)>CODE(C4);D4;C4)) BUT IF C4 contains Cd and D4 contains Ce, then S4 should say Cd, BUT IF C4 contains cd and D4 contains ce, then S4 should say cd, BUT IF C4 contains Ce and D4 contains Cd, then S4 should say Cd, BUT IF C4 contains cd and D4 contains Ce, then S4 should say cd.


Cd sort help-1.xlsx

[nathansav]

=if(mid(c4,1,2)="cd".......

[Polymorpher]

I'm pretty novice about excel, but it appears to me you might be able to use the AND function to make a some sort of logical chain with a bunch of IFs with ANDs to suit your needs. Might be wrong though.

[Saarang84]

Your requirement looks like its a bit complex (from my experience on your other related thread). May you reveal what this entire activity is for? I'm asking because any help you get here you need to convert it to the Swedish version before use.

[hemesh]

@Tinwelende

you are using code which only gives the Number for the first character

for all capital letters i.e. A-Z code starts from 65 and ends at 90
for all small letters code i.e. a-z numbering starts at 97-122.

[Tinwelende]

nathansav: Thank you! I read about that function after you posted it and it seems to be somewhat what I am going to need. Though, is it possible to add to your function: "...and if(mid(d4,1,2)="ce", then C4 should say ce and D4 should say cd"? Is there a function like that?

Polymorpher: That's exactly what I want! I just don't know how to write them.

Saarang84: I am a hamster breeder and I'm creating a document where you can add the known genotype of the parents and it will automatically calculate the genotype of the babies and tell what actual color that genotype is, and in what frequencies it should appear in the litter. What I need in this thread is for a calculation in the middle of all the calculations. I realized that the ce and cd-genes don't get sorted properly with my original formula that is only case sensitive. In this case, I always want to out the cd gene after the ce gene, unless there are two cd or two ce genes. In that case they should only be case sensitive (Ce vs. ce and so on).

hemesh: I'm not sure I understand quite. What type of code are you referring to? (I do understand the system of numbering the letters, though).

[hemesh]

in a cell let's say A1 type C and in another cell lets say B1 type c
then in B1 type = code(A1) and drag to right

now type Cat in A1 and cat in B1.

[Tinwelende]

Oh, ok. So the code for C is 67 and the code for c is 99?

How can I use this in my example above to solve my problem?

[hemesh]

See if below helps

IN R4 copy paste below then drag down
=IF(C4="","",IF(SUMPRODUCT(CODE(MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1)))>SUMPRODUCT(CODE(MID(D4,ROW(INDIRECT("1:"&LEN(D4))),1))),D4,C4))

In S4 copy paste below drag down
=IF(D4="","",IF(SUMPRODUCT(CODE(MID(D4,ROW(INDIRECT("1:"&LEN(D4))),1)))>SUMPRODUCT(CODE(MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1))),D4,C4))

[Tinwelende]

Hmmm... It does sort everything case sensitive, but it still doesn't put cd infront of ce. It seems to work the way the formula I first had does. I've marked the ones that needs to switch places in red:

Help 2.xlsx

[hemesh]

posted other answer

[hemesh]

ok in that case
In R4
=IF(C4="","",IFERROR(IF(MATCH("cd",C4:D4,0)=2,C4,IF(MATCH("cd",C4:D4,0)=1,D4)),IF(SUMPRODUCT(CODE(MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1)))>SUMPRODUCT(CODE(MID(D4,ROW(INDIRECT("1:"&LEN(D4))),1))),D4,C4)))

in S4
=IF(C4="","",IFERROR(IF(MATCH("cd",C4:D4,0)=2,D4,IF(MATCH("cd",C4:D4,0)=1,C4)),IF(SUMPRODUCT(CODE(MID(D4,ROW(INDIRECT("1:"&LEN(D4))),1)))>SUMPRODUCT(CODE(MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1))),D4,C4)))

[Tinwelende]

That still makes cd appear in front of ce.

Help 2.xlsx

[hemesh]

you are using
=IF(C4="","",IFERROR(IF(MATCHA("cd",C4:D4,0)=2,C4,IF(MATCHA("cd",C4:D4,0)=1,D4)),IF(SUMPRODUCT(CODE(MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1)))>SUMPRODUCT(CODE(MID(D4,ROW(INDIRECT("1:"&LEN(D4))),1))),D4,C4)))

It's Match Not MATCHA

[Saarang84]

you are using
=IF(C4="","",IFERROR(IF(MATCHA("cd",C4:D4,0)=2,C4,IF(MATCHA("cd",C4:D4,0)=1,D4)),IF(SUMPRODUCT(CODE(MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1)))>SUMPRODUCT(CODE(MID(D4,ROW(INDIRECT("1:"&LEN(D4))),1))),D4,C4)))

It's Match Not MATCHA

Hey Hemesh,

Tina uses Swedish excel, so I suppose PASSA would be the right function to be used in place of MATCH.

[hemesh]

Hey Hemesh,

Tina uses Swedish excel, so I suppose PASSA would be the right function to be used in place of MATCH.

Hello Saarang then posted formula by the OP should have been
=IF(C4="";"";IF(KOD(C4)>KOD(D4);D4;C4))

if that's the case then
sumproduct will be PRODUKTSUMMA
Code will be KOD
and Match will be PASSA

[Saarang84]

Hello Saarang then posted formula by the OP should have been
=IF(C4="";"";IF(KOD(C4)>KOD(D4);D4;C4))

if that's the case then
sumproduct will be PRODUKTSUMMA
Code will be KOD
and Match will be PASSA

Yes that's correct.
The OP posts her requirements in English in this forum and converts them to Swedish later for use. This thread is related to another one here. See my post #25 there.

[Tinwelende]

Oooooh! I'm so sorry! I translate all the codes I get by hand and I accidentally translated MATCH to MATCHA, since that's actually the Swedish aquivalent of "match". Though, Excel does indeed use the term PASSA instead.

Now it's putting the cells with cd in them at the end, just like it should. Although, it doesn't put the ones with the capital letters infront (e.g. Ce infront of ce and so on). It always needs to put the ones with upper case letters infront of the other. Other than that, it seems to be going the right way! Thank you so much for helping me with this! :D

[Tinwelende]

I solved it myself, by putting the cd and ce- sorting formula and the case sensitive-sorting formula into two different columns. So I let there be two different formulas instead. Works perfectly. So one more time: Thank you so much for all your help! It's worth so much to me! THANK YOU!

[hemesh]

That's great ! another thing you could do is to put an if with exact instead of match like exact("CD",the cell reference"), and condition if that one is true.

Hope that might also help