Excel Logic needs a Doctor !! FORMULA FIX DESIRED!!

[Learning VBA]

I have the following worksheet setup:
Column A = Origin Column C = Destination Column E = Airline
Cell A2: DTW Cell C2: IAH Cell E2: CO
Cell A3: DTW Cell C3: IAH Cell E3: NW
Cell A4: DTW Cell C4: MCO Cell E4: AA
...........
........
..........
18,900 rows follow with similar type of airline schedule data

My objective is three fold:

1.
cell E2 = CO, and Cell A2 or Cell C2 contains origin/destination as DTW,
or MEM, or MSP, I would like the cell on the sheet i.e. Cell F2 = 0

2.
If cell E3 = NW, and Cell A3 or Cell C3 contains Origin/destination as
DTW, or MEM, or MSP, I would like the cell on the sheet i.e. Cell F3 = 1

3.
Finally, if cell E4 = AA, I would like the cell on the sheet i.e. Cell
F4 = 1

I would like to present an example of what I have done so far to achieve
my objective. Let us take Row 2 as an example where I placed 1 formula
in Cell L2:

=IF(E2="NW","NW",IF(OR(E2="CO"),"CO","OTHER"))

and;
another in Cell M3:

=IF(E2="NW",OR(A2="DTW",A2="MEM",A2="MSP",C2="DTW",C2="MEM",C2="MSP"),IF
(OR(E2="CO"),OR(A2="DTW",A2="MEM",A2="MSP",C2="DTW",C2="MEM",C2="MSP"),"
OTHER"))

The result is completely incoherent. I have been unsuccessful and need
some help with the logic and possibly a new formula. If you could
explain a methodology to achieve my desired outcome, and modify my
formulas, or give me a formula, I would appreciate it.

Thank you Excel gurus...........


Thanks.

 

[Dianne]

At this point, you might consider a custom VBA function, like this:

Function MyFunction() As Integer

Dim strAirline As String
Dim strOrigin As String
Dim strDestination As String
Dim blnCheckThis As Boolean
Dim rng As Range

Set rng = ActiveCell

strAirline = rng.Offset(0, -1).Value 'Value in E
strOrigin = rng.Offset(0, -5).Value 'Value in A
strDestination = rng.Offset(0, -3).Value 'Value in C

If strOrigin = "DTW" _
Or strOrigin = "MEM" _
Or strOrigin = "MSP" _
Or strDestination = "DTW" _
Or strDestination = "MEM" _
Or strDestination = "MSP" Then
blnCheckThis = True
End If

Select Case strAirline
Case "CO"
If blnCheckThis Then
MyFunction = 0
Exit Function
End If
Case "NW"
If blnCheckThis Then
MyFunction = 1
Exit Function
End If
Case "AA"
MyFunction = 1
Exit Function
End Select

'This is what the function returns
'if it doesn't meet any of your criteria
MyFunction = 999

End Function

In

 

[Vasant Nanavati]

If I understand you correctly, you need something like this:

=IF(E2="AA",1,IF(OR(A2="DTW",A2="MEM",A2="MSP",C2="DTW",C2="MEM",C2="MSP"),I
F(E2="NW",1,IF(E2="CO",0,"ERROR")),"ERROR"))

Since you haven't said what you want if none of your criteria are met, I
have used "ERROR" for that contingency.

 

[Learning VBA]

MY PREVIOUS POST WAS INCOMPLETE.....check this out


Once again, after your formulas, I think I can now clarify exactly what
I desire from this effort:

Table A2:E8
ORIGIN DESTIN AIR NOW DESIRED
DFW IAH 8W 1 1
PHX IAH HP 1 1
DTW IAH NW 0 1
MKE IAH NW 0 0
PIT IAH CO 1 1
IAH DTW CO 0 0

Desired Column Conditions
Keep (desired value=1) all NW flights with DTW, MEM, or MSP in
origin/destin
Remove (desired value=0) all other NW flights
Remove (desired value=0) all CO flights with DTW, MEM, or MSP in
origin/destin
Keep (desired value=1) all other CO flights
Keep (desired value=1) all other airline flights

Formula in D3 at this time:
=IF((OR(A3="DTW",A3="MEM",A3="MSP",B3="DTW",B3="MEM",B3="MSP")*(OR(C3="C
O",C3="NW")))=1,0,IF(C3<>"CO",IF(C3<>"NW",1,"0"),"1"))

This works for all conditions except the row with DTW, IAH, NW - which
gives me gives a value of 0, when the conditions need it to be =1
For reference puposes, the correct values are presented in Column E,
labeled the "desired" column.

Please help me out on this one, thanks

 

[Learning VBA]

Vasant, Diana: We are getting close...........
After your formulas, I think I can now clarify exactly what I desire
from this effort:

Table A2:E8
ORIGIN
DFW
PHX
DTW
MKE
PIT
IAH

Desired Column Conditions
Keep (desired value=1) all NW flights with DTW, MEM, or MSP in
origin/destin
Remove (desired value=0) all other NW flights
Remove (desired value=0) all CO flights with DTW, MEM, or MSP in
origin/destin
Keep (desired value=1) all other CO flights
Keep (desired value=1) all other airline flights

Formula in D3
=IF((OR(A3="DTW",A3="MEM",A3="MSP",B3="DTW",B3="MEM",B3="MSP")*(OR(C3="C
O",C3="NW")))=1,0,IF(C3<>"CO",IF(C3<>"NW",1,"0"),"1"))

This works for all conditions except the row with DTW, IAH, NW - which
gives me gives a value of 0, when the conditions need it to be =1
For reference puposes, the correct values are presented in Column E,
labeled the "desired" column.


Thanks.

 

[Learning VBA]

Vasant:

Apologize for the multi-post. I have restated the problem, and may be
you could help me out. Your previous response was close, however this is
what I desired. So, here it is:

I think I can clarify exactly what I desire from my nested if effort:

Table A2:E8
ORIGIN DESTIN AIR NOW DESIRED
DFW IAH 8W 1 1
PHX IAH HP 1 1
DTW IAH NW 0 1
MKE IAH NW 0 0
PIT IAH CO 1 1
IAH DTW CO 0 0

Desired Conditions
1. Keep (desired value=1) all NW flights with DTW, or MEM, or MSP in
origin/destin.

2. Remove (desired value=0) all other NW flights.

3. Remove (desired value=0) all CO flights with DTW, or MEM, or MSP in
origin/destin.

4. Keep (desired value=1) all other CO flights.

5. Keep (desired value=1) all other airline flights.

Formula in cell D3 at this time:
=IF((OR(A3="DTW",A3="MEM",A3="MSP",B3="DTW",B3="MEM",B3="MSP")*(OR(C3="C
o",c3="nw")))=1,0,if(c3<>"co",if(c3<>"nw",1,"0"),"1"))

This works for all conditions except the row with DTW, IAH, NW - which
gives me gives a value of 0, when the conditions need it to be =1
For reference puposes, the correct values are presented in Column E,
labeled the "desired" column.

Please help me out on this one, thanks

MAX.

Thanks.

 

[onedaywhen]

Try this formula:

=OR(OR(A2={"DTW","MEM","MSP"},C2={"DTW","MEM","MSP"}),NOT(E2="NW"))*NOT(AND(OR(A2={"DTW","MEM","MSP"},C2={"DTW","MEM","MSP"}),E2="CO"))

 

[Vasant Nanavati]

There has to be a shorter way, but:

=IF(OR(AND(C2="NW",A2<>"MEM'",A2<>"DTW",A2<>"MSP",B2<>"MEM'",B2<>"DTW",B2<>"
MSP"),AND(C2="CO",A2="MEM'",A2="DTW",A2="MSP",B2="MEM'",B2="DTW",B2="MSP")),
0,1)