Matching prices for shopping items

**samlim** · 01-04-2010, 03:06 AM

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

**teylyn** · 01-04-2010, 03:12 AM

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.

**teylyn** · 01-04-2010, 03:24 AM

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!

**samlim** · 01-04-2010, 04:20 AM

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