Extract Minimum Value and keep cell formatting

[LeilaniMerle]

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?

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

<colgroup><col><col><col span="7"><col><col></colgroup><tbody>
</tbody>

 

[hiker95]

LeilaniMerle,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

The suppliers are colour coded

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.

 

[hiker95]

LeilaniMerle,

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

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1		QTY	Supplier 1/Unit	Supplier 1 \Total	Supplier 2/Unit	Supplier 2 \Total	Supplier 3/Unit	Supplier 3 \Total	Supplier 4/Unit	Supplier 4 \Total	Minimum supplier
2	10A Circuit Breaker	10	12.45	124.5	13.66	136.6	17.55	175.5	9.33	93.3
3	25 A Circuit Breaker	10	1.2	12	7.6	76	9	90	5	50
4	Plug Top Arrestor 16A	10	2.4	24	8.3	83	6	60	4	40
5	Connector Strip 16A12W Black	10	6.5	65	9.5	95	8	80	7	70
6	VEC 236 ELB Fluorescent Fitting	20	3.3	66	4.7	94	7	140	8	160
7	VEC 258 ELB Fluorescent Fitting	20	2.3	46	6.2	124	4	80	6	120
8	CLF Genie 11W/827 B22 PHI	150	1.6	240	1.2	180	2	300	2	300
9	CLF Genie 11W/827 E27 PHI	150	5.5	825	4	600	3	450	5	750
10	H/Saw Bld 18T BI-MET NICH	20	6.2	124	3	60	1	20	4	80
11	Hacksaw BLD JUNIOR SPEAR	10	3.9	39	5	50	5	50	1	10
12	SAM CB SF2-G DP 60A	10	7.2	72	7	70	6	60	3	30
13	SAM CB SF3-G3 TP 30A CBI	15	4.6	69	8.9	133.5	9	135	8	120
14	SAM CB SF2-G3 DP 15A CBI	15	1.2	18	8.9	133.5	8	120	9	135
15	Surfix 2,5x3 + E WHI -100m	200	4.3	860	6	1200	4	800	6	1200
16	Ceen Ext Box 4x2 CT9052	20	5.6	112	3.5	70	5	100	5	100
17	Ceen Ext Box 4x4 CT9054	20	7.8	156	2.4	48	2	40	4	80
18	50W GU10 230V 36 Halogen	200	4.1	820	7.6	1520	6	1200	2	400
19	Surfix 1.5 2+E-100m	200	2.2	440	8.1	1620	3	600	1	200
20	Surfix 2.5 2+E-100m	200	10.6	2120	5.3	1060	5	1000	4	800
21	Candle Lamp 240V 40W/60W	200	8.4	1680	9.2	1840	7	1400	5	1000
22	Ballast Osram 2x36 EKB 2QT FIT 8	20	9.7	194	9.6	192	8	160	6	120
23	Term DISCONN FEMALE FLAT	100	6.3	630	7.1	710	5	500	3	300
24	Term DISCONN MALE FLAT BLU	100	5.2	520	4	400	6	600	2	200
25	Lamp 400W HPS SON-T E40	24	4.8	115.2	3	72	4	96	7	168
26	Wall Plug & Screw (Fischer Plugs)	100	9.6	960	5.2	520	2	200	8	800
27	Gland 1 PVC White	20	4.2	84	6.3	126	6	120	9	180
28	Gland 0 PVC White	20	1.3	26	8.5	170	1	20	5	100
29	VEC 218 ELB Fluorescent Fitting	10	6.3	63	1.4	14	3	30	6	60
30	G4-20W-12V Halogen 2000hr	50	4.1	205	8.6	430	9	450	2	100
31	Stove Plate Solid 8"	40	9.5	380	8.7	348	6	240	5	200
32	Stove Solid 6" Solid 6"	40	8.3	332	1.3	52	5	200	2	80
33	Lamp 240V 11W ES Mini	100	9.6	960	4.5	450	2	200	4	400
34	3kw Element & Thermostat KEG3A	15	2.8	42	6.2	93	1	15	3	45
35	3Kw Geyser Spiral & Thermostat	10	1.3	13	1.2	12	4	40	6	60
36	CRC 2005 2.26 Multipurpose	5	7.5	37.5	1.3	6.5	6	30	1	5
37	Self Tapping Screw PAN HD	100	8.6	860	1.4	140	8	800	8	800
38	Self Tapping Screw PAN HD	100	5.8	580	5.6	560	9	900	7	700
39	Self Tap Screw 8x40mm	100	6.9	690	8.9	890	7	700	9	900
40	Fluor Tube 32 W Circline Eurolux	20	4.2	84	7.5	150	5	100	4	80
41	Fluor Tube 40W Circline Eurolux	20	2.2	44	7.4	148	3	60	2	40
42
43	SUB TOTAL EX VAT		219.55	14732.2	237.76	14677.1	217.55	12361.5	197.33	11076.3	197.33
44	14% VAT		30.74	2062.51	33.29	2054.79	30.46	1730.61	27.63	1550.68	27.63
45	TOTAL		250.29	16794.71	271.05	16731.89	248.01	14092.11	224.96	12626.98	224.96
46
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1		QTY	Supplier 1/Unit	Supplier 1 \Total	Supplier 2/Unit	Supplier 2 \Total	Supplier 3/Unit	Supplier 3 \Total	Supplier 4/Unit	Supplier 4 \Total	Minimum supplier
2	10A Circuit Breaker	10	12.45	124.5	13.66	136.6	17.55	175.5	9.33	93.3	9.33
3	25 A Circuit Breaker	10	1.2	12	7.6	76	9	90	5	50	1.2
4	Plug Top Arrestor 16A	10	2.4	24	8.3	83	6	60	4	40	2.4
5	Connector Strip 16A12W Black	10	6.5	65	9.5	95	8	80	7	70	6.5
6	VEC 236 ELB Fluorescent Fitting	20	3.3	66	4.7	94	7	140	8	160	3.3
7	VEC 258 ELB Fluorescent Fitting	20	2.3	46	6.2	124	4	80	6	120	2.3
8	CLF Genie 11W/827 B22 PHI	150	1.6	240	1.2	180	2	300	2	300	1.2
9	CLF Genie 11W/827 E27 PHI	150	5.5	825	4	600	3	450	5	750	3
10	H/Saw Bld 18T BI-MET NICH	20	6.2	124	3	60	1	20	4	80	1
11	Hacksaw BLD JUNIOR SPEAR	10	3.9	39	5	50	5	50	1	10	1
12	SAM CB SF2-G DP 60A	10	7.2	72	7	70	6	60	3	30	3
13	SAM CB SF3-G3 TP 30A CBI	15	4.6	69	8.9	133.5	9	135	8	120	4.6
14	SAM CB SF2-G3 DP 15A CBI	15	1.2	18	8.9	133.5	8	120	9	135	1.2
15	Surfix 2,5x3 + E WHI -100m	200	4.3	860	6	1200	4	800	6	1200	4
16	Ceen Ext Box 4x2 CT9052	20	5.6	112	3.5	70	5	100	5	100	3.5
17	Ceen Ext Box 4x4 CT9054	20	7.8	156	2.4	48	2	40	4	80	2
18	50W GU10 230V 36 Halogen	200	4.1	820	7.6	1520	6	1200	2	400	2
19	Surfix 1.5 2+E-100m	200	2.2	440	8.1	1620	3	600	1	200	1
20	Surfix 2.5 2+E-100m	200	10.6	2120	5.3	1060	5	1000	4	800	4
21	Candle Lamp 240V 40W/60W	200	8.4	1680	9.2	1840	7	1400	5	1000	5
22	Ballast Osram 2x36 EKB 2QT FIT 8	20	9.7	194	9.6	192	8	160	6	120	6
23	Term DISCONN FEMALE FLAT	100	6.3	630	7.1	710	5	500	3	300	3
24	Term DISCONN MALE FLAT BLU	100	5.2	520	4	400	6	600	2	200	2
25	Lamp 400W HPS SON-T E40	24	4.8	115.2	3	72	4	96	7	168	3
26	Wall Plug & Screw (Fischer Plugs)	100	9.6	960	5.2	520	2	200	8	800	2
27	Gland 1 PVC White	20	4.2	84	6.3	126	6	120	9	180	4.2
28	Gland 0 PVC White	20	1.3	26	8.5	170	1	20	5	100	1
29	VEC 218 ELB Fluorescent Fitting	10	6.3	63	1.4	14	3	30	6	60	1.4
30	G4-20W-12V Halogen 2000hr	50	4.1	205	8.6	430	9	450	2	100	2
31	Stove Plate Solid 8"	40	9.5	380	8.7	348	6	240	5	200	5
32	Stove Solid 6" Solid 6"	40	8.3	332	1.3	52	5	200	2	80	1.3
33	Lamp 240V 11W ES Mini	100	9.6	960	4.5	450	2	200	4	400	2
34	3kw Element & Thermostat KEG3A	15	2.8	42	6.2	93	1	15	3	45	1
35	3Kw Geyser Spiral & Thermostat	10	1.3	13	1.2	12	4	40	6	60	1.2
36	CRC 2005 2.26 Multipurpose	5	7.5	37.5	1.3	6.5	6	30	1	5	1
37	Self Tapping Screw PAN HD	100	8.6	860	1.4	140	8	800	8	800	1.4
38	Self Tapping Screw PAN HD	100	5.8	580	5.6	560	9	900	7	700	5.6
39	Self Tap Screw 8x40mm	100	6.9	690	8.9	890	7	700	9	900	6.9
40	Fluor Tube 32 W Circline Eurolux	20	4.2	84	7.5	150	5	100	4	80	4
41	Fluor Tube 40W Circline Eurolux	20	2.2	44	7.4	148	3	60	2	40	2
42
43	SUB TOTAL EX VAT		219.55	14732.2	237.76	14677.1	217.55	12361.5	197.33	11076.3	197.33
44	14% VAT		30.74	2062.51	33.29	2054.79	30.46	1730.61	27.63	1550.68	27.63
45	TOTAL		250.29	16794.71	271.05	16731.89	248.01	14092.11	224.96	12626.98	224.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.

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.

 

[LeilaniMerle]

hiker95, It worked brilliantly. Many many thanks. Hours of worked saved. I am delighted. Chat soon.

Leilani

 

[LeilaniMerle]

hiker95, It worked brilliantly. Many many thanks. Hours of worked saved. I am delighted. Chat soon.

Leilani

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

 

[hiker95]

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
	A	B	C	E	G	I	K	L
1		QTY	Supplier 1/Unit	Supplier 2/Unit	Supplier 3/Unit	Supplier 4/Unit	Minimum supplier
2	10A Circuit Breaker	10	12.45	13.66	17.5	9.33	9.33
3	25 A Circuit Breaker	10	1.2	7.6	9	5	1.2
4	Plug Top Arrestor 16A	10	2.4	8.3	6	4	2.4
5	Connector Strip 16A12W Black	10	6.5	9.5	8	7	6.5
6	VEC 236 ELB Fluorescent Fitting	20	3.3	4.7	7	8	3.3
7	VEC 258 ELB Fluorescent Fitting	20	2.3	6.2	4	6	2.3
8	CLF Genie 11W/827 B22 PHI	150	1.6	1.2	2	2	1.2
Sheet1

Cell Formulas
Range	Formula
K2	=MIN(C2,E2,G2,I2)

If we then run the macro, we get this:

Excel 2007
	A	B	C	E	G	I	K	L
1		QTY	Supplier 1/Unit	Supplier 2/Unit	Supplier 3/Unit	Supplier 4/Unit	Minimum supplier
2	10A Circuit Breaker	10	12.45	13.66	17.5	9.33	9.33
3	25 A Circuit Breaker	10	1.2	7.6	9	5	1.2
4	Plug Top Arrestor 16A	10	2.4	8.3	6	4	2.4
5	Connector Strip 16A12W Black	10	6.5	9.5	8	7	6.5
6	VEC 236 ELB Fluorescent Fitting	20	3.3	4.7	7	8	3.3
7	VEC 258 ELB Fluorescent Fitting	20	2.3	6.2	4	6	2.3
8	CLF Genie 11W/827 B22 PHI	150	1.6	1.2	2	2	1.2
Sheet1

Cell Formulas
Range	Formula
K2	=MIN(C2,E2,G2,I2)

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

Excel 2007
	A	B	C	E	G	I	K	L
1		QTY	Supplier 1/Unit	Supplier 2/Unit	Supplier 3/Unit	Supplier 4/Unit	Minimum supplier
2	10A Circuit Breaker	10	12.45	13.66	1.11	9.33	1.11
3	25 A Circuit Breaker	10	1.2	1.11	9	5	1.11
4	Plug Top Arrestor 16A	10	2.4	8.3	6	4	2.4
5	Connector Strip 16A12W Black	10	6.5	9.5	8	7	6.5
6	VEC 236 ELB Fluorescent Fitting	20	3.3	4.7	7	8	3.3
7	VEC 258 ELB Fluorescent Fitting	20	2.3	6.2	4	6	2.3
8	CLF Genie 11W/827 B22 PHI	150	1.6	1.2	2	2	1.2
Sheet1

Cell Formulas
Range	Formula
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).

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.

 

[LeilaniMerle]

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

 

[hiker95]

LeilaniMerle,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.