Excel = IF ( AND Array Formula

Each week we need to audit 5,000 + rows of data, to determine if the data is Okay or if there is an Error.
Essentially what we need is an IF(AND( formula for 37 combinations, which leads me to believe we need a VBA Vlookup Array formula. But, at this point I'm lost. See attached file.

Any assistance or insight would be greatfully appreciated.

Tosagua
TosaguaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kgerbChief EngineerCommented:
maybe I'm missing it?  did you attach the file?
0
TosaguaAuthor Commented:
Sorry about that.
I'm using Internet Explorer 8, which sometimes doesn't work on EE.

Tosagua
Audit-Spreadsheet.xlsx
0
kgerbChief EngineerCommented:
Okay, I'm a little confused, but let me see if I got this straight.  You want to test the cells in columns D and F for 5000 rows.  

Let's start at the top of your list.  

If the value in column D = 241 and the value in column F is O or T, it's okay.  If the value in column F is I or N, Error.

OR, next line...

if the value in column D = 590 and the value in column F is O or I, it's okay, If the value in column F is T or N, Error.

Is that what you're looking for?  Just want to make sure before I go off and start working on this.

Kyle
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

TosaguaAuthor Commented:
Kyle,

Yes, that is what we are looking for.

Tosagua
0
kgerbChief EngineerCommented:
Tosagua,
Take a look at the example workbook.  It looks through every line on worksheet Testing.  It compares the values of every line to a key range I put on the first worksheet and outputs either Okay or Error on the testing sheet.  Let me know if this is close and we can work out the kinks.

Sub SearchAndCompare()
Dim rStart As Range, rEnd As Range
Dim r As Range
Dim CheckVal As String
Dim FoundIt As Range

Set rStart = Worksheets("Testing").Cells(Rows.Count, "D").End(xlUp).End(xlUp).Offset(1) 'remove .offset(1) if you don't have headers
Set rEnd = Worksheets("Testing").Cells(Rows.Count, "D").End(xlUp)

For Each r In Range(rStart, rEnd)
    CheckVal = r & "-" & r.Offset(, 2)
    Set FoundIt = Range("rngKey").Find(CheckVal, , xlValues, xlWhole)
    If FoundIt Is Nothing Then r.Offset(, 3) = "Error" Else r.Offset(, 3) = FoundIt.Offset(, 1)
Next r
End Sub

Open in new window

28690627-01.xlsm
0
TosaguaAuthor Commented:
Kyle,

1st Kink - The Macro is in a WorkBOOK titled "Invoice Audit Information". The WorkSHEET on which the macro is run is titled "Funding Obligation" (different WorkBOOK).

Can your Key Range, that the macro is using to assign values, be listed as "Key Range" workSHEET in WorkBOOK  "Invoice Audit Information" ?

2nd Kink - Can the output values of the macro be listed in Column W ?

Tosagua
0
kgerbChief EngineerCommented:
Try this.  Let me know what else.

Here's the code and see the example files.

Sub SearchAndCompare()
Dim rStart As Range, rEnd As Range
Dim r As Range
Dim CheckVal As String
Dim FoundIt As Range
Dim fName As Variant, wbFundingObligation As Workbook
Dim ws As Worksheet


fName = Application.GetOpenFilename( _
            FileFilter:="Excel Files (*.xls*), *.xls*", _
            Title:="Pick a Funding Obligation Workbook", _
            MultiSelect:=False)
If fName <> False Then Set wbFundingObligation = Workbooks.Open(fName) Else Exit Sub
Set ws = wbFundingObligation.Worksheets("Invoice Audit Information")

ThisWorkbook.Activate
Set rStart = ws.Cells(Rows.Count, "D").End(xlUp).End(xlUp).Offset(1) 'remove .offset(1) if you don't have headers
Set rEnd = ws.Cells(Rows.Count, "D").End(xlUp)

For Each r In Range(rStart, rEnd)
    CheckVal = r & "-" & r.Offset(, 2)
    Set FoundIt = ThisWorkbook.Sheets("Key Range").Range("rngKey").Find(CheckVal, , xlValues, xlWhole)
    If FoundIt Is Nothing Then ws.Cells(r.Row, "W") = "Error" Else ws.Cells(r.Row, "W") = FoundIt.Offset(, 1)
Next r
End Sub

Open in new window

28690627-01-Invoice-Audit-Information.xl
28690627-01-Some-Workbook.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
one of the things I've done in the past has been to populate columns with the results of intermediate formulas and then combine the results in another column.  This allows me to validate the intermediate results
0
TosaguaAuthor Commented:
Kyle,
I greatly appreciate the assistance.
Thank you,
Tosagua
0
kgerbChief EngineerCommented:
You're welcome :)

Kyle
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.