+ Reply to Thread
Results 1 to 6 of 6

Have excel find matching items from different worksheets and apply price

  1. #1
    Registered User
    Join Date
    10-10-2012
    Location
    El Paso, TX
    MS-Off Ver
    Excel 2007
    Posts
    3

    Have excel find matching items from different worksheets and apply price

    I have 2 different sheets(see attachments. The Inventory Sheet has a list of our items and our cost, but we have a list from our supplier that they sent us with some updated prices. We are in the process of changing item numbers to match the list from the specific supplier, since we are now only making purchases from them. I need a solution to match any items from the 2 lists and change it to the new price. if the item does not match the to leave the price. By the way I'm really new to excel so if you can give me the Dummys explanation.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Have excel find matching items from different worksheets and apply price

    to search for the items in the supplier sheet, use this. it will return the existing price, if the item is not in the new list...

    =IFERROR(VLOOKUP(A2,'[supplier%20list(1).xlsx]Sheet1'!A$4:D$12253,3,FALSE),C2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-10-2012
    Location
    El Paso, TX
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Have excel find matching items from different worksheets and apply price

    Thank you very much for responding, Like I said I'm new to excel. I have no idea where to place the formula.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Have excel find matching items from different worksheets and apply price

    put that in G2, copied down

  5. #5
    Registered User
    Join Date
    10-10-2012
    Location
    El Paso, TX
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Have excel find matching items from different worksheets and apply price

    Thank you so much this was a BIG timesaver. How can we color coordinate if it matched it, have the cell for the corrected price green, and if it it did not find it red. that way we know which items to change to the the suppliers part number.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Have excel find matching items from different worksheets and apply price

    highlight the column with the "tested" values in them, select conditional formating - new rule - use formula

    enter this and select "fill" = green
    =G2<>C2

    select new rule again - use formula and select fill = red
    =C2

+ 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