1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007 data comparison

Discussion in 'Business Applications' started by Lucas2000, Jul 19, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Lucas2000

    Lucas2000 Thread Starter

    Joined:
    Jul 19, 2012
    Messages:
    2
    Hi there,

    I hope you can help. I’m about to start an internet business with a friend and get new price amendments from the wholesaler every couple of months.

    What I want to know is;


    We have around a 1000 products, the supplier has about 10,000 and they can supply us with an Excel doc every couple of months. Is there a way of finding out under product code which products have changed in price in our range of 1000 products, if I were to put one on one tab and one on another? We wouldn’t want to know about the other 9000 products, if this is possible?


    EG.



    Column A

    Product code



    Column B

    Product description



    Column C

    Price Ex VAT
     
  2. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    It is very possible to do this in Excel, although other programs like Access may be better suited. Governing factors are ease and output. In the attached workbook, the Business tab (you) compares the Vendor Product Number, and does the math for the price difference. The Vendor sheet has more products than the Business sheet.
    =INDEX(Vendor!$C$2:$C$7,MATCH(Business!$A$2,Vendor!$A$2:$A$7,0))
    Index (VENDOR PRICES) Match(PRODUCT CODE per Row with VENDOR PRODUCT CODE) using Exact Match. This returns the Index value which is the Price from the New Vendor List.
    Ideally for something like this where formulas can get trampled, I would have a Vendor workbook, A Business Workbook, and a Compare workbook.The Compare workbook would be the one that you open. It would automatically save itself with a new name (Protecting its formulas), import the Business list of the Products you have, then import the Vendor list.Importing from the closed sheets preserves their integrity, and if everything goes bad, opening Compare again will recreate the new workbook in just a few seconds.
    This is not the best method for what you need, but if Excel is what you know to use, it works as an interim while perhaps learning access or SQL and data queries and the like.
    This Compare workbook is not perfect, it is a demo.
     

    Attached Files:

  3. getthecansout

    getthecansout

    Joined:
    Jan 17, 2011
    Messages:
    192
    You could also swap the old Vendor sheet with the new one from your supplier.
    The Business sheet will update itself.
     

    Attached Files:

  4. getthecansout

    getthecansout

    Joined:
    Jan 17, 2011
    Messages:
    192
    But it should work as long as the new sheet from your suppliers is laid out the same as the old one i.e

    the sheet must have the same name ie both are called stock sheets or whatever you call them also
    red pencil is in a1 quantity is in a2 and price is in a3 on BOTH sheets then it will recalculate


    Hope that works for you
     
  5. Lucas2000

    Lucas2000 Thread Starter

    Joined:
    Jul 19, 2012
    Messages:
    2
    Thanks guys,

    Really appreciate your help, I've checked out the formulae and that should apply well to the spreadsheets!

    Lucas
     
  6. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    Good Luck with it. Remember to mark this Solved.
     
  7. getthecansout

    getthecansout

    Joined:
    Jan 17, 2011
    Messages:
    192
    Any more problems with it give us a shout and good look with the business
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1061764