IF formula too much for my brain

[Grammarjunkie]

Thank you for clicking on my thread.

I'm fairly proficient at Excel formulas; however, this one is just becoming too much. I feel a fog settling over my brain.

I have three fields: D10, G10, and J10. All three will have dollar amounts manually entered, and I need a formula turning out various results depending on the combinations of the three.
(i.e. - If D10 is different from G10 and J10, if J10 is different from G10 and D10, if G10 is different from J10 and D10, if all three are different from each other, if J10 is only different from G10, if G10 is only different from D10, and if D10 is only different from J10)
Depending on which is the situation, a different sentence will populate somewhere.
(e.g. - "G10 sales price (XXXXX) does not match J10 sales price (xxxxx)"
"G10 sales price (xxxxx) does not match J10 sales price (xxxxx) or D10 sales price (XXXX)"
etc.)

I don't even know if this possible, but I feel pretty sure it is with some combination of AND(OR) functions. I just am going cross eyed.

So far, I have what is needed if one is different from BOTH of the other two.

=IF(G10="","",IF(J10="","",IF(D10="","",IF(G10<>AND(J10,D10),CONCATENATE("DU Sales price (",(G10),") does not match ELC Sales Price (",(D10),") or the CD Sales Price (",(J10),")"),IF(J10<>AND(G10,D10),CONCATENATE("CD Sales Price (",(J10),") does not match DU Sales Price (",(G10),") or the ELC Sales Price (",(D10),")"),IF(D10<>AND(J10,G10),CONCATENATE("ELC Sales Price (",(D10),") does not match CD Sales Price (",(J10),") or the DU Sales Price (",(G10),")"),""))))))


As you can see, I'm also Concatenating so that the sentence automatically fills in the dollar amounts.

Is this possible, guys? It seems way too complicated and I'm paralyzed. Haha.

Thanks for any and all help! :D Much appreciation!

EDIT: I don't mind doing it myself, really. I like learning. So I'm not outright asking for the entire perfect formula. Just an idea of where to begin. I have ADD so my brain is just not finding a Launchpad. haha.

 

[AndrwT]

Hi Grammarjunkie;

It's possible for sure, no worries about it.

Now, there are probably many ways to do it... unfortunately I don't know the fancy automatic ways via VBA; but if you could share a pic of the sheet so I can better see the whole lot that would help me out because I would go for the multiple ifs also and the formula would definitely be veeeeery long...

I hope someone will be able to share a smoother way, I will learn from it. In the meantime, if you want to share a visual pic we could start building up a formula.

I have to leave in half an hour though...

 

[AndrwT]

Try applying the OR within your formula also. IF(OR(logical 1, logical 2, logical 3);value if true [CONCATENATE function]; value if false [IF function again) ...

 

[AlphaFrog]

You explained three scenarios where you're looking to find the odd man out as it were; which of the three values is different from the other two. Is that correct? What if all three don't match? Would you ever have to compare more than three values? If yes, could you list all the possible results.

 

[Joe4]

Quite frankly, when I get a formula this complex, I find it easier to create my own User Defined Function in VBA to do it. They are much easier to maintain, as they are structured in a way which makes them easier to see all the possibilities and what is going on. If you are open to a VBA solution, let me know.

Regardless of whether you go with a formula or User Defined Function, it would be very helpful if you could just lay out all the different possibilities for us, and what should happen with each possibility.

 

[Max1616]

Try this
=IF(AND(D10<>G10,D10=J10),CONCATENATE("D10 Sales Price (",D10,") does not match G10 Sales Price (",G10,")"),IF(AND(D10=G10,D10<>J10),CONCATENATE("D10 Sales Price (",D10,") does not match J10 Sales Price (",J10,")"),IF(AND(G10=D10,G10<>J10),CONCATENATE("G10 Sales Price (",G10,") does not match J10 Sales Price (",J10,")"),IF(AND(G10<>D10,G10=J10),CONCATENATE("G10 Sales Price (",G10,") does not match D10 Sales Price (",D10,")"),IF(AND(J10=D10,J10<>G10),CONCATENATE("J10 Sales Price (",J10,") does not match G10 Sales Price (",G10,")"),IF(AND(J10<>D10,J10=G10),CONCATENATE("J10 Sales Price (",J10,") does not match D10 Sales Price (",D10,")"),IF(AND(D10<>G10,D10<>J10),CONCATENATE("D10 Sales Prices (",D10,") does not match G10 Sales Price (",G10,") or J10 Sales Price (",J10,")"),IF(AND(G10<>D10,G10<>J10),CONCATENATE("G10 Sales Prices (",G10,") does not match D10 Sales Price (",D10,") or J10 Sales Price (",J10,")"),"All Sales Prices Match"))))))))

 

[Grammarjunkie]

One sec.

 

[Grammarjunkie]

You explained three scenarios where you're looking to find the odd man out as it were; which of the three values is different from the other two. Is that correct? What if all three don't match? Would you ever have to compare more than three values? If yes, could you list all the possible results.

I wouldn't need to compare more than three. Just those three.

Whether none of them match and where 1 doesn't match.
And then nothing needs to happen if they all match.

 

[Grammarjunkie]

Quite frankly, when I get a formula this complex, I find it easier to create my own User Defined Function in VBA to do it. They are much easier to maintain, as they are structured in a way which makes them easier to see all the possibilities and what is going on. If you are open to a VBA solution, let me know.

Regardless of whether you go with a formula or User Defined Function, it would be very helpful if you could just lay out all the different possibilities for us, and what should happen with each possibility.

I'm open to VBA!
However, I've only used really simple VBA. Haha. Like how to hide rows and columns automatically. I've never done anything like this, but I'm open to it!

 

[Grammarjunkie]

Try this
=IF(AND(D10<>G10,D10=J10),CONCATENATE("D10 Sales Price (",D10,") does not match G10 Sales Price (",G10,")"),IF(AND(D10=G10,D10<>J10),CONCATENATE("D10 Sales Price (",D10,") does not match J10 Sales Price (",J10,")"),IF(AND(G10=D10,G10<>J10),CONCATENATE("G10 Sales Price (",G10,") does not match J10 Sales Price (",J10,")"),IF(AND(G10<>D10,G10=J10),CONCATENATE("G10 Sales Price (",G10,") does not match D10 Sales Price (",D10,")"),IF(AND(J10=D10,J10<>G10),CONCATENATE("J10 Sales Price (",J10,") does not match G10 Sales Price (",G10,")"),IF(AND(J10<>D10,J10=G10),CONCATENATE("J10 Sales Price (",J10,") does not match D10 Sales Price (",D10,")"),IF(AND(D10<>G10,D10<>J10),CONCATENATE("D10 Sales Prices (",D10,") does not match G10 Sales Price (",G10,") or J10 Sales Price (",J10,")"),IF(AND(G10<>D10,G10<>J10),CONCATENATE("G10 Sales Prices (",G10,") does not match D10 Sales Price (",D10,") or J10 Sales Price (",J10,")"),"All Sales Prices Match"))))))))

Holy cow. :D Lol, I'm testing it now.. But so far it's working perfectly. I gotta tweak it a bit, but this is definitely what I needed.
You're an EXCELlent person!