Need advice on a new spreadsheet that can show me best price from a particular supplier

Massivebarra

New Member
Joined
Jun 13, 2018
Messages
4
hi,

I have multiple suppliers that I ask them to supply plumbing items that I choose at their best price.

My trouble is the suppliers are becoming more eg. 10 suppliers.

Each supplier can supply their 'own brand' of tap or Pipe

Each supplier may have a different QTY for what they are offering at price.

i want to be able to locate the best price for an item and then somehow order from the spreadsheet without revealing a competitors price to them

I hope this makes sense?

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]SUPPLIER 1[/TD]
[TD]QTY[/TD]
[TD]SUPPLIER 2[/TD]
[TD]QTY[/TD]
[TD]SUPPLIER 3[/TD]
[TD]QTY[/TD]
[TD]SUPPLIER 4[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TAP[/TD]
[TD]$3.10[/TD]
[TD]1000[/TD]
[TD]$3:30[/TD]
[TD]500[/TD]
[TD]$2.70[/TD]
[TD]2000[/TD]
[TD]$3.20[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]PIPE[/TD]
[TD]$10.00[/TD]
[TD]100[/TD]
[TD]$10.50[/TD]
[TD]200[/TD]
[TD]$11.00[/TD]
[TD]100[/TD]
[TD]$10:00[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]CU[/TD]
[TD]$30.50[/TD]
[TD]50[/TD]
[TD]$30.20[/TD]
[TD]50[/TD]
[TD]$31.00[/TD]
[TD]50[/TD]
[TD]$25.50[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]FITTING[/TD]
[TD]$5.00[/TD]
[TD]100[/TD]
[TD]$5.00[/TD]
[TD]100[/TD]
[TD]$5.00[/TD]
[TD]200[/TD]
[TD]$5.50[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]GLUE[/TD]
[TD]$3.70[/TD]
[TD]60[/TD]
[TD]$4.00[/TD]
[TD]50[/TD]
[TD]$3.70[/TD]
[TD]100[/TD]
[TD]$3.70[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]VENT[/TD]
[TD]$8.10[/TD]
[TD]100[/TD]
[TD]$5.00[/TD]
[TD]200[/TD]
[TD]$8.10[/TD]
[TD]100[/TD]
[TD]$7.50[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Can you clarify: What do you mean by "order from the spreadsheet"? and: Are these prices per item or are you wanting to calculate price per item as part of your spreadsheet?
 
Upvote 0
Can you clarify: What do you mean by "order from the spreadsheet"? and: Are these prices per item or are you wanting to calculate price per item as part of your spreadsheet?

Hi Lenny,

I dont want to calculate. Yes the prices are per item. I get different rates depending on how many I buy.

Eg. I buy 10 taps and the supplier will offer the price of $10 per tap but the supplier says if you buy 100 taps then its $5 per tap.

When I say 'order from the spreadsheet' i mean send them the spreadsheet with the quanties i order but not let them see other suppliers pricing.

I also would like to know if there is a way of filtering the prices for each item that shows which supplier has the best price.

I have like 10 suppliers so its really difficult
 
Upvote 0
So your sheet should only have best price & lowest qty per item only?
 
Upvote 0
So your sheet should only have best price & lowest qty per item only?

hi Rasghul,

Price is the key. When I decide I am going to order something like say 500 taps, 300 pipes and 800 glue....I want to be able to look at the spreadsheet and somehow sort so that it can tell me who has the best price or ranked from 1 - 15.

Often I will give the entire order to the same supplier if the $ combination of items I require is acceptable.
 
Upvote 0
I added a helper column J on my sheet to get the lowest price for each row
Code:
MIN(B2,D2,F2,H2)
and then conditionally formatted each supplier column with formula to highlight best prices in each Supplier column.

=$B2=$J2 (for Supplier 1),
=$D2=$J2 (for Supplier 2) etc.

On another sheet you can index/match the items vs the Supplier to create your order page. I data validated the Supplier names so it swaps between prices for each item.

I'm sure there's a better way of doing it, but that's my try. :biggrin:
 
Upvote 0
Ah, so you're trying to keep the whole order with one supplier, right? And you want to know who has the best Total price for the combination of items you want?
 
Upvote 0
One more question: Are the quantities minimum order or fixed lot size? i.e. can you order 800 taps from supplier 2 for $3.30? Or do you have to buy in groups of 500?
 
Upvote 0
Ok I think I've got it. It's complicated, but seems to work nicely and I think you'll like it. Hold on while I figure out how to upload screenshots.
 
Upvote 0
Ok it's not pretty, (it looks better in excel I swear!) but tell me if this is something like what you wanted:

In row 2 you fill in your order quantities. Rows 4-7 show the subtotals (qty x item price) for each supplier. If the order quantity is lower than the minimum, it shows "N/A". If the order field is blank, it stays blank. Column "I" shows the order total for each supplier. If there are any N/A's in that row, it shows N/A (i.e. you can't make that order from that supplier unless you increase quantity). Rows 11-14 are where you put your actual suppliers and prices.

The only thing left is to look at line I and see which is the cheapest total. I think the easiest way to do that would be Conditional Formatting --> Color Scales. (But play with the other conditional formatting options; data bars might be good too.)


Excel 2010
ABCDEFGHIJKL
1TapsPipesFittings(Copy and paste these top two lines for your order)
2Order:1000200
3Total:
4Suppler 131002000 5100
5Supplier 233002100 5400
6Supplier 3N/A2200 N/A
7Supplier 432002000 5200
8
9
10SupplierTap PriceTap QTYPipe PricePipe QtyFitting PriceFitting Qty
11Supplier 1$3.101000$10.00100$5.00100(Fill in price info in this grid)
12Supplier 2$3.30500$10.50200$5.00100
13Supplier 3$2.702000$11.00100$5.00200
14Supplier 4$3.201000$10.00100$5.50100
Sheet1
Cell Formulas
RangeFormula
C4=IF(C$2>0,IF(C$2>=C11,(C$2*B11),"N/A"),"")
C5=IF(C$2>0,IF(C$2>=C12,(C$2*B12),"N/A"),"")
C6=IF(C$2>0,IF(C$2>=C13,(C$2*B13),"N/A"),"")
C7=IF(C$2>0,IF(C$2>=C14,(C$2*B14),"N/A"),"")
E4=IF(E$2>0,IF(E$2>=E11,(E$2*D11),"N/A"),"")
E5=IF(E$2>0,IF(E$2>=E12,(E$2*D12),"N/A"),"")
E6=IF(E$2>0,IF(E$2>=E13,(E$2*D13),"N/A"),"")
E7=IF(E$2>0,IF(E$2>=E14,(E$2*D14),"N/A"),"")
G4=IF(G$2>0,IF(G$2>=G11,(G$2*F11),"N/A"),"")
G5=IF(G$2>0,IF(G$2>=G12,(G$2*F12),"N/A"),"")
G6=IF(G$2>0,IF(G$2>=G13,(G$2*F13),"N/A"),"")
G7=IF(G$2>0,IF(G$2>=G14,(G$2*F14),"N/A"),"")
I4=IF(COUNTIF(C4:G4,"N/A"),"N/A",SUM(C4:G4))
I5=IF(COUNTIF(C5:G5,"N/A"),"N/A",SUM(C5:G5))
I6=IF(COUNTIF(C6:G6,"N/A"),"N/A",SUM(C6:G6))
I7=IF(COUNTIF(C7:G7,"N/A"),"N/A",SUM(C7:G7))
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top