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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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?
TosaguaAuthor Commented:
Sorry about that.
I'm using Internet Explorer 8, which sometimes doesn't work on EE.

Tosagua
Audit-Spreadsheet.xlsx
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

TosaguaAuthor Commented:
Kyle,

Yes, that is what we are looking for.

Tosagua
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
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
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

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
TosaguaAuthor Commented:
Kyle,
I greatly appreciate the assistance.
Thank you,
Tosagua
kgerbChief EngineerCommented:
You're welcome :)

Kyle
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.