validating multiple checks

[virgan]

Please help

There are 2 excel issues which im working on

The first part which i had is like matching unique descriptions to unique nos.

Actualy there will be a matrix which has the no and name.

Daily i pull out a report from an application and match that report with the matrix to ensure that the data in the application matches with the matrix

For example

Matrix
No Name
23 Vendor 1
45 Vendor 2
78 Vendor 3
80 Vendor 5



Report

No Name
23 Vendor 5 Bad
45 Vendor 1 Bad
78 Vendor 3 Good
80 Vendor 1 Bad

This is the validation im trying to implement. The matrix should be the source for this validation.


The second part which i have added is like the below

its a seperate/different kind of match validation.

Matrix

No Vno
001 590
001 998
001 999
002 1000


Report
Report
No Vno
001 1000 Bad
001 1001 Bad
001 1005 Bad


so here its a different scenario a unique no will have more than one Vno.

So i need to check that kind of a validation here.

The matrix should be the source for this validation as well.

[virgan]

need help!

[ChemistB]

How do you plan on implementing this check? Is your report data in Excel but in another workbook? Will you be moving it or importing it into the same workbook where your master list is? The first part of your question should be fairly simple using concatenate and VLookup. I'm still trying to figure out your second validation

[DonkeyOte]

virgan,

if you wish to x-post the same question in multiple forums we would kindly ask you provide links in each forum to the other(s):

http://www.mrexcel.com/forum/showthread.php?t=368279

[virgan]

The data would initialy pulled into another workbook but i will reference the details to the validation work book(sheet2) which im placing in the same folder.

The validation workbook will have the below.

The matrix will be in sheet1 and i will import the data to sheet2 by referencing.

so source of the validation would be the matrix in sheet1.


Second part

is a seperate excel which will have similar set.

The matrix will be saved in sheet 1

and the validation will be done in sheet 2

data will be imported to sheet2

But the check here is different

Matrix

No Vno
001 590
001 998
001 999
002 1000

here if you see a single no can have 3 vendors or more. In this case if i pull up a report like the below what i expect is a validation done based on the above matrix and pull a result as "Bad" or "Good"

Bocs the matrix values do not match with the actual result. for 001 the possible Vno are 590, 998, 999 and not the values as below.

Report

No Vno
001 1000 Bad
001 1001 Bad
001 1005 Bad

[JBeaucaire]

I don't understand the second part, but the first part is straightforward enough. If the matrix is on sheet1, then this formula on another sheet will compare two adjacent cells:

=IF(ISERROR(MATCH(A1&B1,Sheet1!$A$1:$A$4&Sheet1!$B$1:$B$4,0)),"Bad", "Good")

That's an ARRAY formula, so you enter it in the cell to check A1 & B1 and press CTRL-SHIFT-ENTER, you will see braces { } appear around your formula confirming the array is active. The first value is "bad"...but copying that cell downward you will get Good for the 3rd pair.

[virgan]

Hi JBeaucaire

Thanks for your help

Im not sure which part you are not so clear in the second part

To make it simple

Pls look at the below

Sheet 1
Matrix
A1 B1
No Vno
001 590
001 998
001 999
002 1000
002 1001
002 1002
003 2000
003 2001
003 2002


Sheet 2
Report
A1 B1 C1
No Vno Result
002 2001 Bad
001 2002 Bad
001 2003 Bad
002 1000 Good
002 1010 Bad
002 1002 Good
003 590 Bad
001 998 Good
003 999 Bad



so im not sure how i validate these kind of checks and display the result in c1.

[JBeaucaire]

Isn't that just the exact same thing as problem 1 except both ranges are numerical? The same formula I offered to solve the first problem would suffice for this as well, no?

I added this to the attached sheet. The data range for both problems is on Sheet1. The answer for Problem1 is on Sheet2. The answer for Problem2 is on Sheet3. It's the same formula just different ranges.