Extract Minimum Value and keep cell formatting

L

LeilaniMerle

New Member
Joined
Jan 26, 2014
Messages
22
Hi there,

I have a spreadsheet wherein I need to extract the minimum value from 4 Suppliers. The suppliers are colour coded, and i would like the extracted amount to show the suppliers colour code as well as the value.

I am merely using currently the Min function. Can anyone assist me?

[TABLE="width: 993"]
<colgroup><col><col><col span="7"><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]QTY[/TD]
[TD]Supplier 1/Unit[/TD]
[TD]Supplier 1 \Total[/TD]
[TD]Supplier 2/Unit[/TD]
[TD]Supplier 2 \Total[/TD]
[TD]Supplier 3/Unit[/TD]
[TD]Supplier 3 \Total[/TD]
[TD]Supplier 4/Unit[/TD]
[TD]Supplier 4 \Total[/TD]
[TD]Minimum supplier[/TD]
[/TR]
[TR]
[TD]10A Circuit Breaker[/TD]
[TD]10[/TD]
[TD="align: right"]12.45[/TD]
[TD="align: right"]124.5[/TD]
[TD="align: right"]13.66[/TD]
[TD="align: right"]136.6[/TD]
[TD="align: right"]17.55[/TD]
[TD="align: right"]175.5[/TD]
[TD="align: right"]9.33[/TD]
[TD="align: right"]93.3[/TD]
[TD="align: right"]9.33[/TD]
[/TR]
[TR]
[TD]25 A Circuit Breaker[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1.20[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]7.60[/TD]
[TD="align: right"]76.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]90.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]50.00[/TD]
[TD="align: right"]1.20[/TD]
[/TR]
[TR]
[TD]Plug Top Arrestor 16A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2.40[/TD]
[TD="align: right"]24.00[/TD]
[TD="align: right"]8.30[/TD]
[TD="align: right"]83.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]60.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]2.40[/TD]
[/TR]
[TR]
[TD]Connector Strip 16A12W Black[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6.50[/TD]
[TD="align: right"]65.00[/TD]
[TD="align: right"]9.50[/TD]
[TD="align: right"]95.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]80.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]70.00[/TD]
[TD="align: right"]6.50[/TD]
[/TR]
[TR]
[TD]VEC 236 ELB Fluorescent Fitting[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3.30[/TD]
[TD="align: right"]66.00[/TD]
[TD="align: right"]4.70[/TD]
[TD="align: right"]94.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]140.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]160.00[/TD]
[TD="align: right"]3.30[/TD]
[/TR]
[TR]
[TD]VEC 258 ELB Fluorescent Fitting[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2.30[/TD]
[TD="align: right"]46.00[/TD]
[TD="align: right"]6.20[/TD]
[TD="align: right"]124.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]80.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]120.00[/TD]
[TD="align: right"]2.30[/TD]
[/TR]
[TR]
[TD]CLF Genie 11W/827 B22 PHI[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]1.60[/TD]
[TD="align: right"]240.00[/TD]
[TD="align: right"]1.20[/TD]
[TD="align: right"]180.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]300.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]300.00[/TD]
[TD="align: right"]1.20[/TD]
[/TR]
[TR]
[TD]CLF Genie 11W/827 E27 PHI[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]5.50[/TD]
[TD="align: right"]825.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]600.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]450.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]750.00[/TD]
[TD="align: right"]3.00[/TD]
[/TR]
[TR]
[TD]H/Saw Bld 18T BI-MET NICH[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]6.20[/TD]
[TD="align: right"]124.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]60.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]20.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]80.00[/TD]
[TD="align: right"]1.00[/TD]
[/TR]
[TR]
[TD]Hacksaw BLD JUNIOR SPEAR[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3.90[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]50.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]50.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]1.00[/TD]
[/TR]
[TR]
[TD]SAM CB SF2-G DP 60A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7.20[/TD]
[TD="align: right"]72.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]70.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]60.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]3.00[/TD]
[/TR]
[TR]
[TD]SAM CB SF3-G3 TP 30A CBI[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]4.60[/TD]
[TD="align: right"]69.00[/TD]
[TD="align: right"]8.90[/TD]
[TD="align: right"]133.50[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]135.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]120.00[/TD]
[TD="align: right"]4.60[/TD]
[/TR]
[TR]
[TD]SAM CB SF2-G3 DP 15A CBI[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1.20[/TD]
[TD="align: right"]18.00[/TD]
[TD="align: right"]8.90[/TD]
[TD="align: right"]133.50[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]120.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]135.00[/TD]
[TD="align: right"]1.20[/TD]
[/TR]
[TR]
[TD]Surfix 2,5x3 + E WHI -100m[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]4.30[/TD]
[TD="align: right"]860.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]1200.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]800.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]1200.00[/TD]
[TD="align: right"]4.00[/TD]
[/TR]
[TR]
[TD]Ceen Ext Box 4x2 CT9052[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]5.60[/TD]
[TD="align: right"]112.00[/TD]
[TD="align: right"]3.50[/TD]
[TD="align: right"]70.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]3.50[/TD]
[/TR]
[TR]
[TD]Ceen Ext Box 4x4 CT9054[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]7.80[/TD]
[TD="align: right"]156.00[/TD]
[TD="align: right"]2.40[/TD]
[TD="align: right"]48.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]80.00[/TD]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD]50W GU10 230V 36° Halogen[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]4.10[/TD]
[TD="align: right"]820.00[/TD]
[TD="align: right"]7.60[/TD]
[TD="align: right"]1520.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]1200.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]400.00[/TD]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD]Surfix 1.5 2+E-100m[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2.20[/TD]
[TD="align: right"]440.00[/TD]
[TD="align: right"]8.10[/TD]
[TD="align: right"]1620.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]600.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]1.00[/TD]
[/TR]
[TR]
[TD]Surfix 2.5 2+E-100m[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]10.60[/TD]
[TD="align: right"]2120.00[/TD]
[TD="align: right"]5.30[/TD]
[TD="align: right"]1060.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]1000.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]800.00[/TD]
[TD="align: right"]4.00[/TD]
[/TR]
[TR]
[TD]Candle Lamp 240V 40W/60W[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]8.40[/TD]
[TD="align: right"]1680.00[/TD]
[TD="align: right"]9.20[/TD]
[TD="align: right"]1840.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]1400.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]1000.00[/TD]
[TD="align: right"]5.00[/TD]
[/TR]
[TR]
[TD]Ballast Osram 2x36 EKB 2QT FIT 8[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]9.70[/TD]
[TD="align: right"]194.00[/TD]
[TD="align: right"]9.60[/TD]
[TD="align: right"]192.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]160.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]120.00[/TD]
[TD="align: right"]6.00[/TD]
[/TR]
[TR]
[TD]Term DISCONN FEMALE FLAT[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]6.30[/TD]
[TD="align: right"]630.00[/TD]
[TD="align: right"]7.10[/TD]
[TD="align: right"]710.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]300.00[/TD]
[TD="align: right"]3.00[/TD]
[/TR]
[TR]
[TD]Term DISCONN MALE FLAT BLU[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5.20[/TD]
[TD="align: right"]520.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]400.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]600.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD]Lamp 400W HPS SON-T E40[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]4.80[/TD]
[TD="align: right"]115.20[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]72.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]96.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]168.00[/TD]
[TD="align: right"]3.00[/TD]
[/TR]
[TR]
[TD]Wall Plug & Screw (Fischer Plugs)[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]9.60[/TD]
[TD="align: right"]960.00[/TD]
[TD="align: right"]5.20[/TD]
[TD="align: right"]520.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]800.00[/TD]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD]Gland 1 PVC White[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4.20[/TD]
[TD="align: right"]84.00[/TD]
[TD="align: right"]6.30[/TD]
[TD="align: right"]126.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]120.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]180.00[/TD]
[TD="align: right"]4.20[/TD]
[/TR]
[TR]
[TD]Gland 0 PVC White[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1.30[/TD]
[TD="align: right"]26.00[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"]170.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]20.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]1.00[/TD]
[/TR]
[TR]
[TD]VEC 218 ELB Fluorescent Fitting[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6.30[/TD]
[TD="align: right"]63.00[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]14.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]60.00[/TD]
[TD="align: right"]1.40[/TD]
[/TR]
[TR]
[TD]G4-20W-12V Halogen 2000hr[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]4.10[/TD]
[TD="align: right"]205.00[/TD]
[TD="align: right"]8.60[/TD]
[TD="align: right"]430.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]450.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD]Stove Plate Solid 8"[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]9.50[/TD]
[TD="align: right"]380.00[/TD]
[TD="align: right"]8.70[/TD]
[TD="align: right"]348.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]240.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]5.00[/TD]
[/TR]
[TR]
[TD]Stove Solid 6" Solid 6"[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]8.30[/TD]
[TD="align: right"]332.00[/TD]
[TD="align: right"]1.30[/TD]
[TD="align: right"]52.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]80.00[/TD]
[TD="align: right"]1.30[/TD]
[/TR]
[TR]
[TD]Lamp 240V 11W ES Mini[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]9.60[/TD]
[TD="align: right"]960.00[/TD]
[TD="align: right"]4.50[/TD]
[TD="align: right"]450.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]400.00[/TD]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD]3kw Element & Thermostat KEG3A[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2.80[/TD]
[TD="align: right"]42.00[/TD]
[TD="align: right"]6.20[/TD]
[TD="align: right"]93.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]15.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]45.00[/TD]
[TD="align: right"]1.00[/TD]
[/TR]
[TR]
[TD]3Kw Geyser Spiral & Thermostat[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1.30[/TD]
[TD="align: right"]13.00[/TD]
[TD="align: right"]1.20[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]60.00[/TD]
[TD="align: right"]1.20[/TD]
[/TR]
[TR]
[TD]CRC 2005 2.26 Multipurpose[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7.50[/TD]
[TD="align: right"]37.50[/TD]
[TD="align: right"]1.30[/TD]
[TD="align: right"]6.50[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]1.00[/TD]
[/TR]
[TR]
[TD]Self Tapping Screw PAN HD[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]8.60[/TD]
[TD="align: right"]860.00[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]140.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]800.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]800.00[/TD]
[TD="align: right"]1.40[/TD]
[/TR]
[TR]
[TD]Self Tapping Screw PAN HD[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5.80[/TD]
[TD="align: right"]580.00[/TD]
[TD="align: right"]5.60[/TD]
[TD="align: right"]560.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]900.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]700.00[/TD]
[TD="align: right"]5.60[/TD]
[/TR]
[TR]
[TD]Self Tap Screw 8x40mm[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]6.90[/TD]
[TD="align: right"]690.00[/TD]
[TD="align: right"]8.90[/TD]
[TD="align: right"]890.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]700.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]900.00[/TD]
[TD="align: right"]6.90[/TD]
[/TR]
[TR]
[TD]Fluor Tube 32 W Circline Eurolux[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4.20[/TD]
[TD="align: right"]84.00[/TD]
[TD="align: right"]7.50[/TD]
[TD="align: right"]150.00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]80.00[/TD]
[TD="align: right"]4.00[/TD]
[/TR]
[TR]
[TD]Fluor Tube 40W Circline Eurolux[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2.20[/TD]
[TD="align: right"]44.00[/TD]
[TD="align: right"]7.40[/TD]
[TD="align: right"]148.00[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]60.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SUB TOTAL EX VAT[/TD]
[TD] [/TD]
[TD="align: right"]219.55[/TD]
[TD="align: right"]14732.20[/TD]
[TD="align: right"]237.76[/TD]
[TD="align: right"]14677.10[/TD]
[TD="align: right"]217.55[/TD]
[TD="align: right"]12361.50[/TD]
[TD="align: right"]197.33[/TD]
[TD="align: right"]11076.30[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14% VAT[/TD]
[TD] [/TD]
[TD="align: right"]30.74[/TD]
[TD="align: right"]2062.51[/TD]
[TD="align: right"]33.29[/TD]
[TD="align: right"]2054.79[/TD]
[TD="align: right"]30.46[/TD]
[TD="align: right"]1730.61[/TD]
[TD="align: right"]27.63[/TD]
[TD="align: right"]1550.68[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD] [/TD]
[TD="align: right"]250.29[/TD]
[TD="align: right"]16794.71[/TD]
[TD="align: right"]271.05[/TD]
[TD="align: right"]16731.89
[/TD]
[TD="align: right"]248.01
[/TD]
[TD="align: right"]14092.11[/TD]
[TD="align: right"]224.96[/TD]
[TD="align: right"]12626.98
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
LeilaniMerle,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


The suppliers are colour coded
Click to expand...

Based on your text display, we have no way of telling what the cell colors are.


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
LeilaniMerle,

Sample raw data before the macro (the colors in row 1 may not match your colors):


Excel 2007
ABCDEFGHIJK
1QTYSupplier 1/UnitSupplier 1 \TotalSupplier 2/UnitSupplier 2 \TotalSupplier 3/UnitSupplier 3 \TotalSupplier 4/UnitSupplier 4 \TotalMinimum supplier
210A Circuit Breaker1012.45124.513.66136.617.55175.59.3393.3
325 A Circuit Breaker101.2127.676990550
4Plug Top Arrestor 16A102.4248.383660440
5Connector Strip 16A12W Black106.5659.595880770
6VEC 236 ELB Fluorescent Fitting203.3664.79471408160
7VEC 258 ELB Fluorescent Fitting202.3466.21244806120
8CLF Genie 11W/827 B22 PHI1501.62401.218023002300
9CLF Genie 11W/827 E27 PHI1505.5825460034505750
10H/Saw Bld 18T BI-MET NICH206.2124360120480
11Hacksaw BLD JUNIOR SPEAR103.939550550110
12SAM CB SF2-G DP 60A107.272770660330
13SAM CB SF3-G3 TP 30A CBI154.6698.9133.591358120
14SAM CB SF2-G3 DP 15A CBI151.2188.9133.581209135
15Surfix 2,5x3 + E WHI -100m2004.386061200480061200
16Ceen Ext Box 4x2 CT9052205.61123.57051005100
17Ceen Ext Box 4x4 CT9054207.81562.448240480
1850W GU10 230V 36 Halogen2004.18207.61520612002400
19Surfix 1.5 2+E-100m2002.24408.1162036001200
20Surfix 2.5 2+E-100m20010.621205.31060510004800
21Candle Lamp 240V 40W/60W2008.416809.218407140051000
22Ballast Osram 2x36 EKB 2QT FIT 8209.71949.619281606120
23Term DISCONN FEMALE FLAT1006.36307.171055003300
24Term DISCONN MALE FLAT BLU1005.2520440066002200
25Lamp 400W HPS SON-T E40244.8115.23724967168
26Wall Plug & Screw (Fischer Plugs)1009.69605.252022008800
27Gland 1 PVC White204.2846.312661209180
28Gland 0 PVC White201.3268.51701205100
29VEC 218 ELB Fluorescent Fitting106.3631.414330660
30G4-20W-12V Halogen 2000hr504.12058.643094502100
31Stove Plate Solid 8"409.53808.734862405200
32Stove Solid 6" Solid 6"408.33321.3525200280
33Lamp 240V 11W ES Mini1009.69604.545022004400
343kw Element & Thermostat KEG3A152.8426.293115345
353Kw Geyser Spiral & Thermostat101.3131.212440660
36CRC 2005 2.26 Multipurpose57.537.51.36.563015
37Self Tapping Screw PAN HD1008.68601.414088008800
38Self Tapping Screw PAN HD1005.85805.656099007700
39Self Tap Screw 8x40mm1006.96908.989077009900
40Fluor Tube 32 W Circline Eurolux204.2847.51505100480
41Fluor Tube 40W Circline Eurolux202.2447.4148360240
42
43SUB TOTAL EX VAT219.5514732.2237.7614677.1217.5512361.5197.3311076.3197.33
4414% VAT30.742062.5133.292054.7930.461730.6127.631550.6827.63
45TOTAL250.2916794.71271.0516731.89248.0114092.11224.9612626.98224.96
46
Sheet1


After the macro:


Excel 2007
ABCDEFGHIJK
1QTYSupplier 1/UnitSupplier 1 \TotalSupplier 2/UnitSupplier 2 \TotalSupplier 3/UnitSupplier 3 \TotalSupplier 4/UnitSupplier 4 \TotalMinimum supplier
210A Circuit Breaker1012.45124.513.66136.617.55175.59.3393.39.33
325 A Circuit Breaker101.2127.6769905501.2
4Plug Top Arrestor 16A102.4248.3836604402.4
5Connector Strip 16A12W Black106.5659.5958807706.5
6VEC 236 ELB Fluorescent Fitting203.3664.794714081603.3
7VEC 258 ELB Fluorescent Fitting202.3466.212448061202.3
8CLF Genie 11W/827 B22 PHI1501.62401.2180230023001.2
9CLF Genie 11W/827 E27 PHI1505.58254600345057503
10H/Saw Bld 18T BI-MET NICH206.21243601204801
11Hacksaw BLD JUNIOR SPEAR103.9395505501101
12SAM CB SF2-G DP 60A107.2727706603303
13SAM CB SF3-G3 TP 30A CBI154.6698.9133.5913581204.6
14SAM CB SF2-G3 DP 15A CBI151.2188.9133.5812091351.2
15Surfix 2,5x3 + E WHI -100m2004.3860612004800612004
16Ceen Ext Box 4x2 CT9052205.61123.570510051003.5
17Ceen Ext Box 4x4 CT9054207.81562.4482404802
1850W GU10 230V 36 Halogen2004.18207.615206120024002
19Surfix 1.5 2+E-100m2002.24408.11620360012001
20Surfix 2.5 2+E-100m20010.621205.310605100048004
21Candle Lamp 240V 40W/60W2008.416809.2184071400510005
22Ballast Osram 2x36 EKB 2QT FIT 8209.71949.6192816061206
23Term DISCONN FEMALE FLAT1006.36307.1710550033003
24Term DISCONN MALE FLAT BLU1005.25204400660022002
25Lamp 400W HPS SON-T E40244.8115.237249671683
26Wall Plug & Screw (Fischer Plugs)1009.69605.2520220088002
27Gland 1 PVC White204.2846.3126612091804.2
28Gland 0 PVC White201.3268.517012051001
29VEC 218 ELB Fluorescent Fitting106.3631.4143306601.4
30G4-20W-12V Halogen 2000hr504.12058.6430945021002
31Stove Plate Solid 8"409.53808.7348624052005
32Stove Solid 6" Solid 6"408.33321.35252002801.3
33Lamp 240V 11W ES Mini1009.69604.5450220044002
343kw Element & Thermostat KEG3A152.8426.2931153451
353Kw Geyser Spiral & Thermostat101.3131.2124406601.2
36CRC 2005 2.26 Multipurpose57.537.51.36.5630151
37Self Tapping Screw PAN HD1008.68601.4140880088001.4
38Self Tapping Screw PAN HD1005.85805.6560990077005.6
39Self Tap Screw 8x40mm1006.96908.9890770099006.9
40Fluor Tube 32 W Circline Eurolux204.2847.515051004804
41Fluor Tube 40W Circline Eurolux202.2447.41483602402
42
43SUB TOTAL EX VAT219.5514732.2237.7614677.1217.5512361.5197.3311076.3197.33
4414% VAT30.742062.5133.292054.7930.461730.6127.631550.6827.63
45TOTAL250.2916794.71271.0516731.89248.0114092.11224.9612626.98224.96
46
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub GetMinColor()
' hiker95, 01/26/2014, ME753096
Dim r As Long, lr As Long, rng As Range, fminrng As Range
Dim myminv As Double
Application.ScreenUpdating = False
lr = Range("A2").End(xlDown).Row
For r = 2 To lr
  Set rng = Application.Union(Range("C" & r), Range("E" & r), Range("G" & r), Range("I" & r))
  myminv = WorksheetFunction.Min(rng)
  Set fminrng = rng.Find(myminv, LookAt:=xlWhole)
  If Not fminrng Is Nothing Then
    Cells(r, 11) = myminv
    Cells(r, 11).Interior.Color = Cells(1, fminrng.Column).Interior.Color
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetMinColor macro.
 
Upvote 0
LeilaniMerle said:
hiker95, It worked brilliantly. Many many thanks. Hours of worked saved. I am delighted. Chat soon.

Leilani
Click to expand...

Oops, sorry. The macro to get colours worked great. But the minute i run the macro my sum function is overwritten. my sum is e.g. in K2 =MIN(C2,E2,G2,I2).

Please know that the Items and qty's and unit prices etc. are all changeable. So it would need to update value output as new values are entered.

I am working in MS Office Excel 2010

Thanks
Leilani
 
Last edited:
Upvote 0
LeilaniMerle,

Thanks for the formulae - it does make a difference.

Not all columns are shown because we are missing the Total columns formulae.

And, if I understand your correctly.

Sample raw data before the macro (the colors in row 1 may not match your colors) (the BLUE cells are the minimums) (the YELLOW cells are the maximums):


Excel 2007
ABCEGIKL
1QTYSupplier 1/UnitSupplier 2/UnitSupplier 3/UnitSupplier 4/UnitMinimum supplier
210A Circuit Breaker1012.4513.6617.59.339.33
325 A Circuit Breaker101.27.6951.2
4Plug Top Arrestor 16A102.48.3642.4
5Connector Strip 16A12W Black106.59.5876.5
6VEC 236 ELB Fluorescent Fitting203.34.7783.3
7VEC 258 ELB Fluorescent Fitting202.36.2462.3
8CLF Genie 11W/827 B22 PHI1501.61.2221.2
Sheet1
Cell Formulas
RangeFormula
K2=MIN(C2,E2,G2,I2)


If we then run the macro, we get this:


Excel 2007
ABCEGIKL
1QTYSupplier 1/UnitSupplier 2/UnitSupplier 3/UnitSupplier 4/UnitMinimum supplier
210A Circuit Breaker1012.4513.6617.59.339.33
325 A Circuit Breaker101.27.6951.2
4Plug Top Arrestor 16A102.48.3642.4
5Connector Strip 16A12W Black106.59.5876.5
6VEC 236 ELB Fluorescent Fitting203.34.7783.3
7VEC 258 ELB Fluorescent Fitting202.36.2462.3
8CLF Genie 11W/827 B22 PHI1501.61.2221.2
Sheet1
Cell Formulas
RangeFormula
K2=MIN(C2,E2,G2,I2)


Then if we change the YELLOW cells, and, run the macro again we get this:


Excel 2007
ABCEGIKL
1QTYSupplier 1/UnitSupplier 2/UnitSupplier 3/UnitSupplier 4/UnitMinimum supplier
210A Circuit Breaker1012.4513.661.119.331.11
325 A Circuit Breaker101.21.11951.11
4Plug Top Arrestor 16A102.48.3642.4
5Connector Strip 16A12W Black106.59.5876.5
6VEC 236 ELB Fluorescent Fitting203.34.7783.3
7VEC 258 ELB Fluorescent Fitting202.36.2462.3
8CLF Genie 11W/827 B22 PHI1501.61.2221.2
Sheet1
Cell Formulas
RangeFormula
K2=MIN(C2,E2,G2,I2)


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Option Explicit
Sub GetMinColorV2()
' hiker95, 01/27/2014, ME753096
Dim lr As Long, c As Range, rng As Range, fminrng As Range
Application.ScreenUpdating = False
lr = Range("K2").End(xlDown).Row
With Range("K2:K" & lr).Interior
  .Pattern = xlNone
  .TintAndShade = 0
  .PatternTintAndShade = 0
End With
For Each c In Range("K2:K" & lr)
  If c <> "" Then
    Set rng = Application.Union(Range("C" & c.Row), Range("E" & c.Row), Range("G" & c.Row), Range("I" & c.Row))
    Set fminrng = rng.Find(c.Value, LookAt:=xlWhole)
    If Not fminrng Is Nothing Then
      c.Interior.Color = Cells(1, fminrng.Column).Interior.Color
    End If
  End If
Next c
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetMinColorV2 macro.
 
Last edited:
Upvote 0
hiker I want to thank you so much. You have been terrific. All is working just as I need it to. You are a genius. Thank you for your patience with me, and for speaking a layman's language so I could understand. No doubt I will be frequenting these forums. So see you soon. You too, have an awesome day... LM x x x
 
Upvote 0
LeilaniMerle,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Reference Current Non Blank Cell - Monthly Calendar
2
Replies
13
Views
872
FERG NATION
F
F
  • Locked
  • Question Question
Check and copy most current non blank cell over the course of a month
Replies
4
Views
580
Fluff
Fluff
A
  • Solved
Add elements for adjacent cells by insert column based on multiple characters
2
Replies
17
Views
660
felixstraube
F
S
  • Solved
NEED AN EQUATION FOR FINDING THE VALUE
Replies
5
Views
413
Peter_SSs
Peter_SSs
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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