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.

 

[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"))))))))

And I was right: my brain never would have been able to begin doing that. Haha.

I mean, I used something similar before, and it totally makes sense to do every combination using = and <>.. For some reason my brain didn't even think of going that route.
I was in too deep in the problem and needed that outside perspective.

 

[Max1616]

Glad I was able to help!

Sincerely,
Max

 

[Sgdva]

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!

Quick Intro:
Press Alt+F11 > (in the Toolbar) Insert > Module
Paste this

Function DifferenceAmongRangeValues(iRange As Range, iTextForDifference1 As String, iTextForDifference2 As String, iTextForDifference3 As String) As String'This only works if the selected Range has values!
'it works for difference only (if values are equal just ignore)
Dim ItemRange As Range
Dim ItemToEvalaute As Range
Dim EvaluatingValue As Long
Dim EvaluatingAddress As String
Dim StringToAdd As String
Dim iBuildedString As String
Const StringToHighLightValue1 = "("
Const StringToHighLightValue2 = ")"
If IsEmpty(iRange) = False And (iRange.Rows.Count > 1 Or iRange.Columns.Count > 1) Then '1. If IsEmpty(iRange) = False
For Each ItemRange In iRange
EvaluatingValue = ItemRange.Value
EvaluatingAddress = ItemRange.Address
For Each ItemToEvalaute In iRange
On Error GoTo xNoValue
If EvaluatingValue <> ItemToEvalaute.Value Then _
StringToAdd = EvaluatingAddress & " " & iTextForDifference1 & " " & StringToHighLightValue1 & EvaluatingValue & StringToHighLightValue2 & _
" " & iTextForDifference2 & " " & ItemToEvalaute.Address & " " & iTextForDifference3 & " " & StringToHighLightValue1 & ItemToEvalaute.Value & StringToHighLightValue2 '2. If EvaluatingValue <> ItemToEvalaute.Value Then
If InStr(iBuildedString, StringToAdd) = 0 Then iBuildedString = StringToAdd & Chr(10) & iBuildedString '3. If InStr(iBuildedString, StringToAdd) = 0
Next ItemToEvalaute
Next ItemRange
DifferenceAmongRangeValues = iBuildedString
End If '1. IsEmpty(iRange) = False
If 1 = 2 Then '2. If 1 = 2
'this is to handleErr
xNoValue:
DifferenceAmongRangeValues = xlErrNA
End If '2. If 1 = 2
End Function

It will work for 2 or more Comparisons in the range
Important note: There has to be only values in the range selected
When you type "=" in excel it will show up as any other native excel formula, Example to call it:

=DifferenceAmongRangeValues(A2:D2;A5;B5;C5)

(Note my formula has local settings ";" instead of commas, adjust to your Local)
Where
A2=100
B2=120
C2=150
D2=180
A5=sales price
B5=does not match
C5=sales price

TIP: If you don't want to have the Text arguments in a cell, you may type them directly on the formula

=DifferenceAmongRangeValues(A2:D2;"sales price";"does not match";"sales price")

Example Result:
$D$2 sales price (180) does not match $C$2 sales price (150)
$D$2 sales price (180) does not match $B$2 sales price (120)
$D$2 sales price (180) does not match $A$2 sales price (100)
$C$2 sales price (150) does not match $D$2 sales price (180)
$C$2 sales price (150) does not match $B$2 sales price (120)
$C$2 sales price (150) does not match $A$2 sales price (100)
$B$2 sales price (120) does not match $D$2 sales price (180)
$B$2 sales price (120) does not match $C$2 sales price (150)
$B$2 sales price (120) does not match $A$2 sales price (100)
$A$2 sales price (100) does not match $D$2 sales price (180)
$A$2 sales price (100) does not match $C$2 sales price (150)
$A$2 sales price (100) does not match $B$2 sales price (120)

(There may be Alt Tab Values laying there, so, if you'd like to clean them, call this macro after calculations have been performed)

Sub Clean_Spaces()
For iClean = 1 To 50
    Cells.Replace What:=Chr(10) & " ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
            Cells.Replace What:="   ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Cells.Replace What:=Chr(10) & Chr(10), Replacement:=Chr(10), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Next iClean
End Sub

 

[Sgdva]

I couldn't edit my previous response in time, I realized there may be some undesired behavior:
Basically bold lines are the same "scenario":
$D$2 sales price (180) does not match $C$2 sales price (150)
$D$2 sales price (180) does not match $B$2 sales price (120)
$D$2 sales price (180) does not match $A$2 sales price (100)
$C$2 sales price (150) does not match $D$2 sales price (180)
$C$2 sales price (150) does not match $B$2 sales price (120)
To prevent it, use this code instead for the Function:

Function DifferenceAmongRangeValues(iRange As Range, iTextForDifference1 As String, iTextForDifference2 As String, iTextForDifference3 As String) As String
'This only works if the selected Range has values!
'it works for difference only (if values are equal just ignore)
Dim ItemRange As Range
Dim ItemToEvalaute As Range
Dim EvaluatingValue As Long
Dim EvaluatingAddress As String
Dim StringToAdd As String
Dim StringAlternative As String
Dim iBuildedString As String
Const StringToHighLightValue1 = "("
Const StringToHighLightValue2 = ")"
If IsEmpty(iRange) = False And (iRange.Rows.Count > 1 Or iRange.Columns.Count > 1) Then '1. If IsEmpty(iRange) = False
For Each ItemRange In iRange
EvaluatingValue = ItemRange.Value
EvaluatingAddress = ItemRange.Address
For Each ItemToEvalaute In iRange
On Error GoTo xNoValue
If EvaluatingValue <> ItemToEvalaute.Value Then '2. If EvaluatingValue <> ItemToEvalaute.Value Then
StringToAdd = EvaluatingAddress & " " & iTextForDifference1 & " " & StringToHighLightValue1 & EvaluatingValue & StringToHighLightValue2 & _
" " & iTextForDifference2 & " " & ItemToEvalaute.Address & " " & iTextForDifference3 & " " & StringToHighLightValue1 & ItemToEvalaute.Value & StringToHighLightValue2
'Previous cycles may add the same string just with ranges inverted, to avoid it we check the alternative statement
StringAlternative = ItemToEvalaute.Address & " " & iTextForDifference1 & " " & StringToHighLightValue1 & ItemToEvalaute.Value & StringToHighLightValue2 & _
" " & iTextForDifference2 & " " & EvaluatingAddress & " " & iTextForDifference3 & " " & StringToHighLightValue1 & EvaluatingValue & StringToHighLightValue2
End If '2. If EvaluatingValue <> ItemToEvalaute.Value Then
If InStr(iBuildedString, StringToAdd) = 0 And InStr(iBuildedString, StringAlternative) = 0 Then iBuildedString = StringToAdd & Chr(10) & iBuildedString '3. If InStr(iBuildedString, StringToAdd) = 0
Next ItemToEvalaute
Next ItemRange
DifferenceAmongRangeValues = iBuildedString
End If '1. IsEmpty(iRange) = False
If 1 = 2 Then '2. If 1 = 2
'this is to handleErr
xNoValue:
DifferenceAmongRangeValues = xlErrNA
End If '2. If 1 = 2
End Function

Result:

$B$2 sales price (120) does not match $D$2 sales price (150)
$B$2 sales price (120) does not match $C$2 sales price (150)
$A$2 sales price (100) does not match $D$2 sales price (150)
$A$2 sales price (100) does not match $C$2 sales price (150)
$A$2 sales price (100) does not match $B$2 sales price (120)

 

[Grammarjunkie]

Glad I was able to help!

Sincerely,
Max

Actually,

Hopefully you're still willing and able to help, but it doesn't quite do what I was hoping it would. (Sorry I'm just now saying this. I was in a meeting.)

See, I had made something similar, but I forgot why it wouldn't work until I started testing yours.
For example:

D10 is $40,000
G10 is $25,000
J10 is $25,000.


With the formula you gave me, in this instance, I want it to read, "D10 sales price (40000) does not match G10 sales price ($25000) or J10 sales price ($25000)."
But your formula (and mine) only turns up "G10 Sales Price (25000) does not match D10 sales price (40000)"

The difference being, it's not comparing the odd man out to the others, it's comparing one of the others to the odd man out... Please tell me that makes sense? Lol.

And now my mind is back to that fog where I'm not sure what to do. I can barely word the question without having to think about it. I have no idea how to address it.

 

[Sgdva]

Actually,

Hopefully you're still willing and able to help, but it doesn't quite do what I was hoping it would. (Sorry I'm just now saying this. I was in a meeting.)

See, I had made something similar, but I forgot why it wouldn't work until I started testing yours.
For example:

D10 is $40,000
G10 is $25,000
J10 is $25,000.


With the formula you gave me, in this instance, I want it to read, "D10 sales price (40000) does not match G10 sales price ($25000) or J10 sales price ($25000)."
But your formula (and mine) only turns up "G10 Sales Price (25000) does not match D10 sales price (40000)"

The difference being, it's not comparing the odd man out to the others, it's comparing one of the others to the odd man out... Please tell me that makes sense? Lol.

And now my mind is back to that fog where I'm not sure what to do. I can barely word the question without having to think about it. I have no idea how to address it.

Did you try the posted UDF above?
If so you may have problems setting the range if it's not consistent (or text among)
Added so you can use it like that.
D10=40000
G10=25000
J10=25000

Function DifferenceAmongRangeValues(iRange As Range, iTextForDifference1 As String, iTextForDifference2 As String, iTextForDifference3 As String) As String
'This only works if the selected Range has values!
'it works for difference only (if values are equal just ignore)
Dim ItemRange As Range
Dim ItemToEvalaute As Range
Dim EvaluatingValue As Long
Dim EvaluatingAddress As String
Dim StringToAdd As String
Dim StringAlternative As String
Dim iBuildedString As String
Const StringToHighLightValue1 = "("
Const StringToHighLightValue2 = ")"
If IsEmpty(iRange) = False And (iRange.Rows.Count > 1 Or iRange.Columns.Count > 1) Then '1. If IsEmpty(iRange) = False
For Each ItemRange In iRange
If IsNumeric(ItemRange.Value) = True Then '4. If IsNumber(ItemRange.Value) = True
EvaluatingValue = ItemRange.Value
EvaluatingAddress = ItemRange.Address
For Each ItemToEvalaute In iRange
On Error GoTo xNoValue
If IsNumeric(ItemToEvalaute.Value) = True Then '5. If IsNumber(ItemToEvalaute.Value)
If EvaluatingValue <> ItemToEvalaute.Value Then '2. If EvaluatingValue <> ItemToEvalaute.Value Then
StringToAdd = EvaluatingAddress & " " & iTextForDifference1 & " " & StringToHighLightValue1 & EvaluatingValue & StringToHighLightValue2 & _
" " & iTextForDifference2 & " " & ItemToEvalaute.Address & " " & iTextForDifference3 & " " & StringToHighLightValue1 & ItemToEvalaute.Value & StringToHighLightValue2
'Previous cycles may add the same string just with ranges inverted, to avoid it we check the alternative statement
StringAlternative = ItemToEvalaute.Address & " " & iTextForDifference1 & " " & StringToHighLightValue1 & ItemToEvalaute.Value & StringToHighLightValue2 & _
" " & iTextForDifference2 & " " & EvaluatingAddress & " " & iTextForDifference3 & " " & StringToHighLightValue1 & EvaluatingValue & StringToHighLightValue2
End If '2. If EvaluatingValue <> ItemToEvalaute.Value Then
If InStr(iBuildedString, StringToAdd) = 0 And InStr(iBuildedString, StringAlternative) = 0 Then iBuildedString = StringToAdd & Chr(10) & iBuildedString '3. If InStr(iBuildedString, StringToAdd) = 0
End If '5. If IsNumber(ItemToEvalaute.Value)
Next ItemToEvalaute
End If '4. If IsNumber(ItemRange.Value) = True
Next ItemRange
DifferenceAmongRangeValues = iBuildedString
End If '1. IsEmpty(iRange) = False
If 1 = 2 Then '2. If 1 = 2
'this is to handleErr
xNoValue:
DifferenceAmongRangeValues = xlErrNA
End If '2. If 1 = 2
End Function

Formula on book:
=DifferenceAmongRangeValues(D10:J10;"sales price";"does not match";"sales price")
Result:
$D$10 sales price (40000) does not match $J$10 sales price (25000)
$D$10 sales price (40000) does not match $G$10 sales price (25000)

 

[Max1616]

Try this:
=IF(AND(D10<>G10,D10=J10),CONCATENATE("G10 Sales Price (",G10,") does not match D10 Sales Price (",D10,") or J10 Sales Price (",J10,")"),IF(AND(D10=G10,D10<>J10),CONCATENATE("J10 Sales Price (",J10,") does not match D10 Sales Price (",D10,") or G10 Sales Price (",G10,")"),IF(AND(G10=D10,G10<>J10),CONCATENATE("J10 Sales Price (",J10,") does not match D10 Sales Price (",D10,") or G10 Sales Price (",G10,")"),IF(AND(G10<>D10,G10=J10),CONCATENATE("D10 Sales Price (",D10,") does not match G10 Sales Price (",G10,") or J10 Sales Price (",J10,")"),IF(AND(J10=D10,J10<>G10),CONCATENATE("G10 Sales Price (",G10,") does not match D10 Sales Price (",D10,") or J10 Sales Price (",J10,")"),IF(AND(J10<>D10,J10=G10),CONCATENATE("D10 Sales Price (",D10,") does not match G10 Sales Price (",G10,") or J10 Sales Price (",J10,")"),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"))))))))