+ Reply to Thread
Results 1 to 8 of 8

validating multiple checks

  1. #1
    Registered User
    Join Date
    02-02-2009
    Location
    chennai,india
    MS-Off Ver
    Excel 2003
    Posts
    4

    validating multiple checks

    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.

  2. #2
    Registered User
    Join Date
    02-02-2009
    Location
    chennai,india
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: validating multiple checks

    need help!

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: validating multiple checks

    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
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: validating multiple checks

    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

  5. #5
    Registered User
    Join Date
    02-02-2009
    Location
    chennai,india
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: validating multiple checks

    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

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: validating multiple checks

    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.
    Last edited by JBeaucaire; 02-03-2009 at 07:23 AM. Reason: Removed book, use one further down.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  7. #7
    Registered User
    Join Date
    02-02-2009
    Location
    chennai,india
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: validating multiple checks

    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.

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: validating multiple checks

    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.

    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1