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

M

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]
 
Sort by date Sort by votes
lennywatson said:
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
tapspipesfittings(copy and paste these top two lines for your order)
order:
total:
suppler 1
supplier 2
supplier 3n/an/a
supplier 4
suppliertap pricetap qtypipe pricepipe qtyfitting pricefitting qty
supplier 1(fill in price info in this grid)
supplier 2
supplier 3
supplier 4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[td="align: Center"]1[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Right"][/td]

[td="align: Right"][/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]2[/td]

[td="align: Right"][/td]
[td="align: Right"]1000[/td]
[td="align: Right"][/td]
[td="align: Right"]200[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]3[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]4[/td]

[td="align: Right"][/td]
[td="align: Right"]3100[/td]
[td="align: Right"][/td]
[td="align: Right"]2000[/td]
[td="align: Right"][/td]

[td="align: Right"][/td]
[td="align: Right"]5100[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]5[/td]

[td="align: Right"][/td]
[td="align: Right"]3300[/td]
[td="align: Right"][/td]
[td="align: Right"]2100[/td]
[td="align: Right"][/td]

[td="align: Right"][/td]
[td="align: Right"]5400[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]6[/td]

[td="align: Right"][/td]

[td="align: Right"][/td]
[td="align: Right"]2200[/td]
[td="align: Right"][/td]

[td="align: Right"][/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]7[/td]

[td="align: Right"][/td]
[td="align: Right"]3200[/td]
[td="align: Right"][/td]
[td="align: Right"]2000[/td]
[td="align: Right"][/td]

[td="align: Right"][/td]
[td="align: Right"]5200[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]8[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]9[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]10[/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]11[/td]

[td="align: Right"]$3.10 [/td]
[td="align: Right"]1000[/td]
[td="align: Right"]$10.00 [/td]
[td="align: Right"]100[/td]
[td="align: Right"]$5.00 [/td]
[td="align: Right"]100[/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]12[/td]

[td="align: Right"]$3.30 [/td]
[td="align: Right"]500[/td]
[td="align: Right"]$10.50 [/td]
[td="align: Right"]200[/td]
[td="align: Right"]$5.00 [/td]
[td="align: Right"]100[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]13[/td]

[td="align: Right"]$2.70 [/td]
[td="align: Right"]2000[/td]
[td="align: Right"]$11.00 [/td]
[td="align: Right"]100[/td]
[td="align: Right"]$5.00 [/td]
[td="align: Right"]200[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]14[/td]

[td="align: Right"]$3.20 [/td]
[td="align: Right"]1000[/td]
[td="align: Right"]$10.00 [/td]
[td="align: Right"]100[/td]
[td="align: Right"]$5.50 [/td]
[td="align: Right"]100[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

</tbody>
sheet1​

[table="width: 85%"]
<tbody>[tr]
[td]worksheet formulas[table="width: 100%"]
<thead>[tr="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]
[th="width: 10px"]cell[/th]
[th="align: Left"]formula[/th]
[/tr]
</thead><tbody>[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]c4[/th]
[td="align: Left"]=if(c$2>0,if(c$2>=c11,(c$2*b11),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]c5[/th]
[td="align: Left"]=if(c$2>0,if(c$2>=c12,(c$2*b12),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]c6[/th]
[td="align: Left"]=if(c$2>0,if(c$2>=c13,(c$2*b13),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]c7[/th]
[td="align: Left"]=if(c$2>0,if(c$2>=c14,(c$2*b14),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]e4[/th]
[td="align: Left"]=if(e$2>0,if(e$2>=e11,(e$2*d11),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]e5[/th]
[td="align: Left"]=if(e$2>0,if(e$2>=e12,(e$2*d12),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]e6[/th]
[td="align: Left"]=if(e$2>0,if(e$2>=e13,(e$2*d13),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]e7[/th]
[td="align: Left"]=if(e$2>0,if(e$2>=e14,(e$2*d14),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]g4[/th]
[td="align: Left"]=if(g$2>0,if(g$2>=g11,(g$2*f11),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]g5[/th]
[td="align: Left"]=if(g$2>0,if(g$2>=g12,(g$2*f12),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]g6[/th]
[td="align: Left"]=if(g$2>0,if(g$2>=g13,(g$2*f13),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]g7[/th]
[td="align: Left"]=if(g$2>0,if(g$2>=g14,(g$2*f14),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]i4[/th]
[td="align: Left"]=if(countif(c4:g4,"n/a"),"n/a",sum(c4:g4))[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]i5[/th]
[td="align: Left"]=if(countif(c5:g5,"n/a"),"n/a",sum(c5:g5))[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]i6[/th]
[td="align: Left"]=if(countif(c6:g6,"n/a"),"n/a",sum(c6:g6))[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]i7[/th]
[td="align: Left"]=if(countif(c7:g7,"n/a"),"n/a",sum(c7:g7))[/td]
[/tr]
</tbody>[/table]
[/td]
[/tr]
</tbody>[/table]
Click to expand...
thanks lenny....you've helped me out alot. I may have more questions as i build this spreadsheet
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
You must log in or register to reply here.

Similar threads

K
  • Solved
Macro to compare old prices with new prices for stock and show(-/+)
4 5 6
Replies
58
Views
686
Peter_SSs
Peter_SSs
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

Share this page

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