IF(AND(Complex Formula in Error in data import before I process it

[JeanPierre Charron]

The data integrity of my monthly imports is a shame but I have to put up with it.
In brief, I have to retrieve the Vendor Name From the Vendor Field (Column AG)
IF the Vendor name is missing THEN
IF the Line_Desc Field is populated Then
Vendor Field (Column AG) = Line_Desc Field (Col D)
ELSE
Vendor Field (Column AG) = "NoID"
ENDIF
ENDIF

First, I was testing my formula in Excel before inserting in VBA, Without
the AND condition and it worked as follows :
=IF(LEFT(L167,1)="P","E",IF(LEFT(L167,1)="C",AG167,IF(LEFT(L167,1)="M",AG167,IF(LEFT(L167,1)="R",AG167))))
Next time I will try to simplify the above formula with an OR condition.
..
Then I inserted my AND condition but I got an Error I cannot detect it
in the following formula :
=IF(LEFT(L169,1)="P","E",IF(AND(LEFT(169,1)="C",AG169="(Blank Value)"),"NoID",IF(LEFT(L169,1)="C",AG169,IF(LEFT(L169,1)="M",AG169,IF(LEFT(L169,1)="R",AG169))),D169)
..
Your help will be very much appreciated.
Have a good day,
J.P.

 

[Claus Busch]

Hi J.P.,

Am Fri, 30 Jan 2015 18:57:02 -0800 (PST) schrieb JeanPierre Charron:

=IF(LEFT(L169,1)="P","E",IF(AND(LEFT(169,1)="C",AG169="(Blank Value)"),"NoID",IF(LEFT(L169,1)="C",AG169,IF(LEFT(L169,1)="M",AG169,IF(LEFT(L169,1)="R",AG169))),D169)

I hope I understood your problem correctly.
Try:
=IF(OR(OR(LEFT(L169,1)={"P";"E"}),AND(LEFT(L169,1)="C",AG169="")),"NoID",IF(OR(LEFT(L169,1)={"C";"M";"R"}),AG169,D169))


Regards
Claus B.

 

[JeanPierre Charron]

Thanks, you are very close, at least you don't have an error.
I will have to re-evaluate your sequence to correct your formula. Not obvious at first sight.
here is the exact case :
Col D Col AG Row #
MATLS, SUPPLIES, TOOLS, EQUIP HEAT EXCHA-003 1
HEAT EXCHANGER SYSTEMS INC (Blank Value) 2

Row# 1
Cell AG,1 is not Null or blank or empty and does not contain the text "(Blank Value)", ok continue to next row.
..
Row # 2
Cell AG,2 is Null or blank or empty or contain the text "(Blank Value)", then check content of cell D2
if Cell D2 is not Null or blank or empty and does not contain the text "(Blank Value)", Then Cell AG2 = D1
Else Cell AG2 = "NoID"

Thank you again
J.P.

 

[Claus Busch]

Hi J.P.,
Am Sat, 31 Jan 2015 06:23:13 -0800 (PST) schrieb JeanPierre Charron:

Col D Col AG Row #
MATLS, SUPPLIES, TOOLS, EQUIP HEAT EXCHA-003 1
HEAT EXCHANGER SYSTEMS INC (Blank Value) 2

you cannot do this with formula because you don't wnat to change the
value in AG if it is correct.
Try:

Sub Test()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, "D").End(xlUp).Row
For Each rngC In .Range("AG1:AG" & LRow)
If Len(rngC) = 0 Or rngC = "" Or rngC = "(Blank Value)" Then
If Len(.Cells(rngC.Row, "D")) > 0 And _
.Cells(rngC.Row, "D") <> "(Blank Value)" Then
rngC = .Cells(rngC.Row, "D")
Else
rngC = "NoID"
End If
End If
Next
End With

End Sub


Regards
Claus B.

 

[JeanPierre Charron]

I understand your comment this is why temporarily I was posting my test result
away from Column AG, by my syntax was wrong.

Thank you from Louisiana
JP.

 

[JeanPierre Charron]

Good Morning Claus,
I would like, each time a "NoID" is generated, to copy/add the whole row to a new "NoID" worksheet.
..
For the sake of efficiency, is it better to do it inside the current loop,
or start a new loop at the end of the current one ?
..
At the beginning of this program the "NoID" worksheet would have to be Emptied.
..............
Since you briefly talked about Ramstein, are you in the Air Force yourself ?
I was a submariner 45 years ago.

Have a good day,
JP.

 

[JeanPierre Charron]

I tried to insert a new condition when I test rngC,Row as shown below :
If Left(.Cells(rngC.Row, "L"), 1) = "P" Then
rngC = "E"
Else
rngC = "NoID"
End If
but it did not work. I do not see what I did wrong in my syntax.
,
Sub CleanUP()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, "D").End(xlUp).Row
For Each rngC In .Range("AG1:AG" & LRow)
If Len(rngC) = 0 Or rngC = "" Or rngC = "(Blank Value)" Then
If Len(.Cells(rngC.Row, "D")) > 0 And _
.Cells(rngC.Row, "D") <> "(Blank Value)" Then
rngC = .Cells(rngC.Row, "D")
Else
If Left(.Cells(rngC.Row, "L"), 1) = "P" Then
rngC = "E"
Else
rngC = "NoID"
End If
End If
End If
Next
End With
End Sub
Thank you again
JP.

 

[Claus Busch]

Hi J.P.,

Am Sun, 1 Feb 2015 09:44:50 -0800 (PST) schrieb JeanPierre Charron:

I tried to insert a new condition when I test rngC,Row as shown below :
If Left(.Cells(rngC.Row, "L"), 1) = "P" Then
rngC = "E"
Else
rngC = "NoID"
End If

try:

Sub CleanUP()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, "D").End(xlUp).Row
For Each rngC In .Range("AG1:AG" & LRow)
If Len(rngC) = 0 Or rngC = "" Or rngC = "(Blank Value)" Then
If Len(.Cells(rngC.Row, "D")) > 0 And _
.Cells(rngC.Row, "D") <> "(Blank Value)" Then
rngC = .Cells(rngC.Row, "D")
ElseIf Left(.Cells(rngC.Row, "L"), 1) = "P" Then
rngC = "E"
Else
rngC = "NoID"
End If
End If
Next
End With
End Sub

If that is not the expected solution please send me a workbook with
different entries in D, L and AG and the expected output in AH.
claus_busch(at)t-online.de

I am not in the army. I am a retired mechanical engineer.


Regards
Claus B.

 

[jean-yves.hemlin]

Le vendredi 30 janvier 2015 21:57:12 UTC-5, JeanPierre Charron a écrit :

=IF(LEFT(L169,1)="P","E",IF(AND(LEFT(169,1)="C",AG169="(Blank Value)"),"NoID",IF(LEFT(L169,1)="C",AG169,IF(LEFT(L169,1)="M",AG169,IF(LEFT(L169,1)="R",AG169))),D169)

Lets play with your indentation to figure out what went wrong in your formula. To do that, I'll respect the structure:
IF( conditions,
TRUE,
FALSE
)

So, here it is:
=IF( LEFT(L169,1)="P",
"E",
IF( AND(
LEFT(169,1)="C",
AG169="(Blank Value)"
),
"NoID",
IF( LEFT(L169,1)="C",
AG169,
IF( LEFT(L169,1)="M",
AG169,
IF( LEFT(L169,1)="R",
AG169)
)
)