IF Statement Help Needed

[sshaffer99]

Hello,</SPAN>

I am trying to create an IF statement for my “Notes” column but I can’t figure out how.</SPAN>

I have 3 Yes/No Columns that show exceptions - LoanExcept, AddrExcept,GARExcept. I’d like to populate 1 Note field that indicates which columns have a “Yes” - see example below.</SPAN>

I assume I can use an IF statement in my Notes column, but not sure how to code it. Any ideas? </SPAN>
Thanks for your help.

Below are the combinations:

A<o></o>	B<o></o>	C<o></o>	D<o></o>
LoanExcept<o></o>	AddrExcept<o></o>	GARExcept<o></o>	Notes<o></o>
Y<o></o>	N<o></o>	N<o></o>	Exception for Loan<o></o>
Y<o></o>	Y<o></o>	N<o></o>	Exception for Loan, Addr<o></o>
Y<o></o>	Y<o></o>	Y<o></o>	Exception for Loan, Addr, GAR<o></o>
N<o></o>	Y<o></o>	N<o></o>	Exception for Addr<o></o>
N<o></o>	Y<o></o>	Y<o></o>	Exception for Add, GAR<o></o>
N<o></o>	N<o></o>	Y<o></o>	Exception for GAR<o></o>

<TBODY>
</TBODY>

 

[BobUmlas]

Use column E as a helper column. In E2:
=IF(A2="Y","Loan","")&IF(B2="Y",", Addr","")&IF(C2="Y",", GAR","")
fill down.
In D2:
=IF(E2<>"","Exception for "&IF(LEFT(E2,1)=",",MID(E2,3,100),E2),"")
Fill down.
Hide column E

 

[Joe4]

Assuming that the data starts on row 2, so your first formula should appear in D2, you could place this formula in cell D2:

=IF(COUNTIF(A2:C2,"Y")>0,"Exception for " & IF(A2="Y","Loan, ","") & IF(B2="Y","Addr, ","") & IF(C2="Y","Gar, ",""),"")

Now, this gets you almost what you want, except there will be a comma and space at the end of each string (where there is at least one "Y"). If this is a problem, you can do one of the following:
1. Use this as a "helper" column, and place this in cell E2 (you can then hide column D, if you like):

=IF(LEN(D2)>0,LEFT(D2,LEN(D2)-2),"")

2. Combine these two formulas in one cell (simply use the formula above in step 1, but replacing every reference to cell D2 with the formula originally posted for cell D2 above). That formula will look a bit messy, and could be a bear to edit, if you ever need to.

Quite frankly, my preferred method of handling situations like this is to create my own function using VBA that does exactly what I want, and is easier to view and edit. The function would look something like this:

Function MyConcat(myRange As Range) As String


    Dim myString As String
    Dim myPhrase As String
    Dim cell As Range
    
'   Loop through all cells in Range
    For Each cell In myRange
'       Check to see if value is "Y"
        If cell = "Y" Then
'           If so, capture the value you want to return
            Select Case cell.Column
                Case 1  'for column A
                    myPhrase = "Loan, "
                Case 2  'for column B
                    myPhrase = "Addr, "
                Case 3  'for column C
                    myPhrase = "Gar, "
            End Select
'           Build second part of string
            myString = myString & myPhrase
        End If
    Next cell
    
'   If any Y values found, return entire phrase
    If Len(myString) > 0 Then
        MyConcat = "Exception for " & Left(myString, Len(myString) - 2)
    Else
        MyConcat = ""
    End If
    
End Function

Then, to use it on my spreadsheet, simply enter this formula in cell D2:

=MyConcat(A2:C2)

So that gives you a few options to work from. There are some brilliant formula gurus on this forum who if the see this, may be able to come up with a better formula, but all these options I provided will work.

 

[martindwilson]

try
="exception for "&LOOKUP(A2&B2&C2,{"NNN","NNY","NYN","NYY","YNN","YYN","YYY"},{"none","GAR","Addr","Add, GAR","Loan","Loan, Addr","Loan, Addr, GAR"})

 

[Joe4]

Nice solution Martin!

 

[martindwilson]

there are actually 8 combos plus 1 for all blanks

Excel Workbook
	A	B	C
1	y	y	y
2	y	y	n
3	y	n	y
4	y	n	n
5	n	y	y
6	n	n	y
7	n	y	n
8	n	n	n
Sheet1

so maybe


=IF(A1&B1&C1="","","Exception for "&LOOKUP(A1&B1&C1,{"nnn","nny","nyn","nyy","ynn","yny","yyn","yyy"},{"NONE","Gar","Addr","Addr,Gar","Loan","Loan,Gar","Loan,Addr","Loan,Addr,Gar"}))

 

[Teeroy]

Here's another approach as an Array formula. It treats the selection as a binary number and uses Choose.

=CHOOSE(SUM((A7:C7="Y")*2^(COLUMN(A7:C7)-1))+1,"NONE","Loan","Addr","Loan,Addr","Gar","Loan,Gar","Addr,Gar","Loan,Addr,Gar")

confirm with CTRL + SHIFT + Enter.

I don't think it's any better than Martin's solution, but I had an idea and wanted to see if I could make it work .

 

[martindwilson]

then another rethink
="exception for "&SUBSTITUTE(TRIM(IF(A2="y","loan"," ")&" "&IF(B2="y","addr"," ")&" "&IF(C2="y","gar",""))," ",",")

 

[sshaffer99]

Wow, GREAT Ideas! Thank you all so much. I ended up using Martin's idea, but may go to Joe4's idea to creating a function if it gets much more complicated. I didn't think of the situation if any of the 3 fields are null, or blank. I may make them mandatory, or add some logic to say if A or B or C are blank/Null, then "Missing data".

Again, thank you all.