+ Reply to Thread
Results 1 to 5 of 5

How to check two values in tab 1 are the same in tab 2

  1. #1
    Registered User
    Join Date
    06-14-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    6

    Smile How to check two values in tab 1 are the same in tab 2

    Hi everyone,

    I am looking for suggestions around how to solve the following challenge.

    Spreadsheet data validation:
    tab 1) list of items with various columns, two of them being: column C has product id, column E has user id
    tab 2) list of items with various columns, two of them being: column A has product id, column J has user id

    I have two functions to identify:
    1) tab 1> column C product-id value exists in tab 2> column A product-id column

    =IF(ISERROR(VLOOKUP(C2,Export!$A$2:$A$1701,1,0)),"no","yes")



    2) tab 1> column E user-id value exists in tab 2> column J user-id column

    =IF(ISERROR(VLOOKUP(E2,Export!$J$2:$J$1701,1,0)),"no","yes")


    However, what i really want to do is the following:
    1) is the record in tab 1> product-id belonging to tab 1> user-id also found in tab 2?

    My functions above only tell me that the records exist, they dont tell me whether the records of user+product in tab 1 is also found in tab 2.

    Tab 2 data comes from a different application, that's why i need to identify inconsistencies in tab 2.

    I hope this makes sense and clarifies what i am trying to do.

    I would consider myself a newbie writing functions in Excel, so your advice around how to go about doing this will be very appreciated.
    I would also prefer to stick to functions rather than writing VB

    Thanks
    Vane

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: How to check two values in tab 1 are the same in tab 2

    can you attach a sample spreadsheet

    can the user ID and the Product ID appear more than once for the same item ?

    if so you can use an array lookup and concatenate the columns
    in Tab1
    =VLOOKUP(C2&E2,Sheet2!A1:A18&Sheet2!J1:J18,1,FALSE)

    And in Tab2
    =VLOOKUP(A2&J2,Sheet1!C1:C18&Sheet1!E1:E18,1,FALSE)

    both need to be added as an Array
    so use Control+Shift+Enter to get {} around the formula

    that will tell you if they exist and return a #N/A if not found
    so you can then use an IF for your yes and no

    see attached, i have used the IF & Iserror and Vlookup to return Yes and No sheet1 column A and Sheet2 Column L
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    06-14-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to check two values in tab 1 are the same in tab 2

    Hi Wayne,

    Many thanks for your reply and the sample spreadsheet, it is perfect, I tested it with different values (changing the values for product id and user id)and your function works great.

    I will attach the v2 of the sample spreadsheet for you and others to see the test results (searching for the attach document button).

    I will now try to implement the same logic to the real spreadsheet which has thousands of records, cross fingers everything will be ok.

    Best,
    Vane
    Last edited by visualuk; 07-11-2013 at 05:19 AM.

  4. #4
    Registered User
    Join Date
    06-14-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    6

    Cool Re: How to check two values in tab 1 are the same in tab 2

    I found the attach file button. See v2 solution file attached.
    Thanks again, Wayne
    Attached Files Attached Files

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: How to check two values in tab 1 are the same in tab 2

    your welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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