+ Reply to Thread
Results 1 to 5 of 5

Crosschecking data

  1. #1
    Registered User
    Join Date
    02-04-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Crosschecking data

    Hi Everyone!

    Got a bit of a complicated one so any help would be greatly appreciated!

    Basically, I have two sheets of data... each contain a unique item number and a price of this item.....

    I want to compare the data to firstly make sure that the unique number is on both sheets and if it is I want it to ensure the prices on both sheets are the same.

    Can anyone help?

    Many Thanks

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

    Re: Crosschecking data

    Column A contains IDs
    Column B contains prices
    Row1 is titles, so list actually starts at row 2

    On sheet1, put this formula in C2 and copy down as far as needed:

    =IF(ISNUMBER(MATCH($A2, Sheet2!$A:$A, 0)), IF(INDEX(Sheet2!$B:$B, MATCH($A2, Sheet2!$A:$A, 0)) = $B2, "Good", "Price Different"), "Product Missing")

    On sheet2, put this formula in C2 and copy down:

    =IF(ISNUMBER(MATCH($A2, Sheet1!$A:$A, 0)), IF(INDEX(Sheet1!$B:$B, MATCH($A2, Sheet1!$A:$A, 0)) = $B2, "Good", "Price Different"), "Product Missing")
    Attached Files Attached Files
    _________________
    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!)

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,307

    Re: Crosschecking data

    Hi,

    With Item numbers in A and prices in B in both sheets, use
    Please Login or Register  to view this content.
    and copy this down. Where it returns #N/A it means the item number doesn't exist.

    HTH
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    02-04-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Crosschecking data

    thanks guys, it really helped!

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

    Re: Crosschecking data

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

+ 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