Vlookup? with optimum pricing based on quantities

[owj]

Hello
The Scenario:
the company I work for have four main customers who near the end of every month will e-mail a list of the stock they want, the maximum quantity they can accept and the price they can afford to pay per unit.

Say for example we have 28 units of one item and the customers have emailed us to saying this

customer 1 --> 10 units at £54.10/ea
customer 2 --> 100 units at £53.90/ea
customer 3 --> 15 units at £54.50/ea
customer 4--> 50 units at £52.50/ea

Now currently in the excel template I am trying to create I have a space for the total number of stock we have (in the example 28) and a two columns for each customer, one saying the price and another with the quantity. This could be a master style sheet and 4 more sheets each one with a blank invoice on it waiting for it to be populated by the stock they can pay the most for.

Obviously, as a company we want to sell as many as we can for the highest price so we would sell 15 to customer 3 for £54.50/ea, 10 to customer 1 for £54.10/ea and the remaining three to customer 2 for £53.90/ea

So my question is, how would I be able to make excel to rank the data so that the customer who is prepared to pay the most gets up to their maximum quantity and the rest (upto the max) goes to the other customers

Any suggestions?
Currently i have just created a table no formulas at all currently!

 

[shg]

Can you not just sort descending by price and allocate from the top?

 

[AD_Taylor]

If you want to go the formula route you can try the layout I have below:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Stock Levels	28
2						Customer Name	Send x units	Units remaining	Price
3	Customer Name	Required Units	Price per Unit	Price Rank				28
4	Customer 1	10	£54.10	2		Customer 3	15	13	£817.50
5	Customer 2	100	£53.90	3		Customer 1	10	3	£541.00
6	Customer 3	15	£54.50	1		Customer 2	3	0	£161.70
7	Customer 4	50	£52.50	4		Customer 4	0	0	£0.00
8						TOTAL	28		£1,520.20
Sheet1

Cell Formulas
Range	Formula
D4	=RANK($C4, $C$4:$C$7)
H3	=$B$1
F4	=INDEX($A$4:$A$7, MATCH(ROW($D1), $D$4:$D$7, 0))
G4	=IF($H3 >= INDEX($B$4:$B$7,MATCH(ROW($D1), $D$4:$D$7, 0)), INDEX($B$4:$B$7, MATCH(ROW($D1), $D$4:$D$7, 0)),$H3)
I4	=$G4 * INDEX($C$4:$C$7, MATCH(ROW($D1), $D$4:$D$7, 0))

The left side contains all of your static data, with a rank column so that we know which customer has the highest rate.
Then the right side has a number of formulas which use this rank to calculate who gets what units. Just realised this will hit a small snag if 2 customers offer the same rate. I'll try and sort something for you that fixes that. But if all the rates offered are unique then this will work.

Hope this helps!

 

[AD_Taylor]

The formulas below sort the same rate issue. All that needed changing was the rank formula so that it pulled out the correct ranks. This does mean however that if 2 customers offer the same rate, then the customer that comes first in the list will get their units allocated first.

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Stock Levels	28
2	Customer Name	Required Units	Price per Unit	Price Rank		Customer Name	Send x units	Units remaining	Price
3				0				28
4	Customer 1	10	£54.10	2		Customer 3	15	13	£817.50
5	Customer 2	100	£53.90	3		Customer 1	10	3	£541.00
6	Customer 3	15	£54.50	1		Customer 2	3	0	£161.70
7	Customer 4	50	£52.50	4		Customer 4	0	0	£0.00
8						TOTAL	28		£1,520.20
Sheet1

Cell Formulas
Range	Formula
D4	=RANK($C4, $C$4:$C$7) + COUNTIF(D3:$D$3, RANK($C4, $C$4:$C$7))
H3	=$B$1
F4	=INDEX($A$4:$A$7, MATCH(ROW($D1), $D$4:$D$7, 0))
G4	=IF($H3 >= INDEX($B$4:$B$7,MATCH(ROW($D1), $D$4:$D$7, 0)), INDEX($B$4:$B$7, MATCH(ROW($D1), $D$4:$D$7, 0)),$H3)
I4	=$G4 * INDEX($C$4:$C$7, MATCH(ROW($D1), $D$4:$D$7, 0))

 

[AD_Taylor]

Realised you might not be able to see all formulas. Hopefully this is my final post

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Stock Levels	28
2	Customer Name	Required Units	Price per Unit	Price Rank		Customer Name	Send x units	Units remaining	Price
3				0				28
4	Customer 1	10	£54.10	2		Customer 3	15	13	£817.50
5	Customer 2	100	£53.90	3		Customer 1	10	3	£541.00
6	Customer 3	15	£54.50	1		Customer 2	3	0	£161.70
7	Customer 4	50	£52.50	4		Customer 4	0	0	£0.00
8						TOTAL	28		£1,520.20
Sheet1

Cell Formulas
Range	Formula
D4	=RANK($C4, $C$4:$C$7) + COUNTIF(D3:$D$3, RANK($C4, $C$4:$C$7))
H3	=$B$1
H4	=IF($H3 - $G4 < 0, 0, $H3 - $G4)
F4	=INDEX($A$4:$A$7, MATCH(ROW($D1), $D$4:$D$7, 0))
G4	=IF($H3 >= INDEX($B$4:$B$7,MATCH(ROW($D1), $D$4:$D$7, 0)), INDEX($B$4:$B$7, MATCH(ROW($D1), $D$4:$D$7, 0)),$H3)
G8	=SUM(G4:G7)
I4	=$G4 * INDEX($C$4:$C$7, MATCH(ROW($D1), $D$4:$D$7, 0))
I8	=SUM(I4:I7)