+ Reply to Thread
Results 1 to 4 of 4

Matching prices for shopping items

  1. #1
    Registered User
    Join Date
    01-03-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Matching prices for shopping items

    Hi everyone,

    I am doing some excel analysis for a university assignment, and I would be very grateful of any help on a problem I have.

    The analysis basically has 2 data components to it:
    The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.

    The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.

    What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:

    1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)
    2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet by lest magnitude)
    3) Pull the price for this "closest quantity"

    I have uploaded a worksheet showing the structure of that data.
    http://www.megaupload.com/?d=DALNF9LG

    I have also actually made an attempt at doing this, and come up with the correct output. But, it seems way too complicated for something which is so simple, so I am hoping one of you excel gurus can help me make it more simpler.

    Is there some VB code I need to do this, or can it just be a few simple formulaes?

    Many Thanks,
    Sam

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Uni Assingment help - matching prices for shopping items

    Hi samlim,

    welcome to the forum.

    please use the forum's facilities to upload a small data sample in a workbook that illustrates your problem. You'll find that people are not much inclined to download from untrusted web sites where the helper will also be subjected to an artificial wait period before the download unless they are prepared to pay a membership fee.

    You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Uni Assingment help - matching prices for shopping items

    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

  4. #4
    Registered User
    Join Date
    01-03-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Uni Assingment help - matching prices for shopping items

    Apologies all - 2 obvious mistakes already when posting on these forums. This is my first time posting, and I appreciate the time and usefulness of this resource, so trying my best to abide by the rules which help this forum function so well.

    I have now uploaded the file as part of this forum.

    Please be aware I have also posted this question to the following forums (apologies, I have been posting away, but not realising the implications of not making people aware of this. It is now obvious to me that cross posting may be waste people's time given the question may have already been answered elsewhere)
    http://www.mrexcel.com/forum/showthread.php?t=438807
    http://www.ozgrid.com/forum/showthread.php?t=141754
    http://chandoo.org/forums/topic/uni-...shopping-items
    http://www.excelforum.com/excel-gene...ing-items.html

    Thanks for understanding,
    Sam
    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