Highlight lowest value in row, calculate each column based on lowest values, print results

[xdriver]

There are more values in the sheet, so I will need to work in the selected ranges. I have pricing from different vendors (V1-x) for the same product, (Product 1-x) as there are hundreds of products. I would like to highlight the lowest value in each row in green (so I can visually see which vendor is the lowest price for that product), and the highest value in red. Then, I would like to do a calculation at the bottom of each column that only sums the items in the column if they are the lowest price.

While not necessary but it would be great, is there a way to create another sheet that "prints" a list of the product, lowest price and the vendor that sells it, so I can place an order with each vendor while getting the best prices?

Thank you so much.

	V1	V2	V3	V4
Product 1	5.25	3.5		5.99
Product 2	26.65	21.699		29.85
Product 3	32.64	32.929		33.75
Product 4	21.6	19		22.9
Product 5	13.68	13		13.9
Product 6	26.65	21.699		29.85
Product 7	32.64	32.929		33.75
Product 8	21.59	19.164		22.85
Product 9	21.76	17.276		22.5
Product 10	21.6	19		22.9
Product 11	32.64	32.929		33.75
Product 12	81.96	85.579		87.48
Product 13	48.96	37.299		53.5
Product 14	40.96	40.96		39.5
Product 15	65.88	68.684		64.68
Product 16	58.94	58.005		62.5
Product 17	25.23	26.672		27.5
Product 18	231.84	231.84		251.72
Product 19	122.94	122.94		131.22
Product 20	81.96	85.579		87.48

 

[kweaver]

Have you tried
1. Selecting B2:E21
2. Conditional format using a formula: =B2=MIN($B2:$E21) and set it to green then =B2=MAX($B2:$E2) set to red?

Book1
	A	B	C	D	E
1		V1	V2	V3	V4
2	Product 1	5.25	3.5		5.99
3	Product 2	26.65	21.699		29.85
4	Product 3	32.64	32.929		33.75
5	Product 4	21.6	19		22.9
6	Product 5	13.68	13		13.9
7	Product 6	26.65	21.699		29.85
8	Product 7	32.64	32.929		33.75
9	Product 8	21.59	19.164		22.85
10	Product 9	21.76	17.276		22.5
11	Product 10	21.6	19		22.9
12	Product 11	32.64	32.929		33.75
13	Product 12	81.96	85.579		87.48
14	Product 13	48.96	37.299		53.5
15	Product 14	40.96	40.96		39.5
16	Product 15	65.88	68.684		64.68
17	Product 16	58.94	58.005		62.5
18	Product 17	25.23	26.672		27.5
19	Product 18	231.84	231.84		251.72
20	Product 19	122.94	122.94		131.22
21	Product 20	81.96	85.579		87.48
Sheet14

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	A	B	C	D	E
1		V1	V2	V3	V4
2	Product 1	5.25	3.5		5.99
3	Product 2	26.65	21.699		29.85
4	Product 3	32.64	32.929		33.75
5	Product 4	21.6	19		22.9
6	Product 5	13.68	13		13.9
7	Product 6	26.65	21.699		29.85
8	Product 7	32.64	32.929		33.75
9	Product 8	21.59	19.164		22.85
10	Product 9	21.76	17.276		22.5
11	Product 10	21.6	19		22.9
12	Product 11	32.64	32.929		33.75
13	Product 12	81.96	85.579		87.48
14	Product 13	48.96	37.299		53.5
15	Product 14	40.96	40.96		39.5
16	Product 15	65.88	68.684		64.68
17	Product 16	58.94	58.005		62.5
18	Product 17	25.23	26.672		27.5
19	Product 18	231.84	231.84		251.72
20	Product 19	122.94	122.94		131.22
21	Product 20	81.96	85.579		87.48
Lists

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E21	Expression	=MAX($B2:$E2)=B2	text	NO
B2:E21	Expression	=MIN($B2:$E2)=B2	text	NO

 

[alansidman]

If you can accept a slightly different presentation, then I have a Power Query Solution for you.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"V1", type number}, {"V2", type number}, {"V3", type any}, {"V4", type number}}),
    #"Inserted Maximum" = Table.AddColumn(#"Changed Type", "Maximum", each List.Max({[V1], [V2], [V4]}), type number),
    #"Inserted Minimum" = Table.AddColumn(#"Inserted Maximum", "Minimum", each List.Min({[V1], [V2], [V4]}), type number)
in
    #"Inserted Minimum"

Book7
	A	B	C	D	E	F	G
1	Column1	V1	V2	V3	V4	Maximum	Minimum
2	Product 1	5.25	3.5		5.99	5.99	3.5
3	Product 2	26.65	21.699		29.85	29.85	21.699
4	Product 3	32.64	32.929		33.75	33.75	32.64
5	Product 4	21.6	19		22.9	22.9	19
6	Product 5	13.68	13		13.9	13.9	13
7	Product 6	26.65	21.699		29.85	29.85	21.699
8	Product 7	32.64	32.929		33.75	33.75	32.64
9	Product 8	21.59	19.164		22.85	22.85	19.164
10	Product 9	21.76	17.276		22.5	22.5	17.276
11	Product 10	21.6	19		22.9	22.9	19
12	Product 11	32.64	32.929		33.75	33.75	32.64
13	Product 12	81.96	85.579		87.48	87.48	81.96
14	Product 13	48.96	37.299		53.5	53.5	37.299
15	Product 14	40.96	40.96		39.5	40.96	39.5
16	Product 15	65.88	68.684		64.68	68.684	64.68
17	Product 16	58.94	58.005		62.5	62.5	58.005
18	Product 17	25.23	26.672		27.5	27.5	25.23
19	Product 18	231.84	231.84		251.72	251.72	231.84
20	Product 19	122.94	122.94		131.22	131.22	122.94
21	Product 20	81.96	85.579		87.48	87.48	81.96
Table1

 

[xdriver]

I have update my profile, thank you for the recommendation.
I have done the conditional formatting highlighting with the suggested expressions but its the tally I am struggling with.
I was able to use =SUM(IF(C2:C21<B2:B21,C2:C21)) when I just had two columns, but now that I have multiple ones I am not sure what to do.
The "power query" would be a fine solution, but I still need to see a total of the lowest values and which vendor has them. And then if possible print a "pick list" of sorts to make ordering easy.

 

[kweaver]

If you use CF to provide an interior color, I believe you'll have to use VBA to identify those cells.
See if this is what you want or can be adapted for your needs.

Book1
	A	B	C	D	E
1		V1	V2	V3	V4
2	Product 1	5.25	3.5		5.99
3	Product 2	26.65	21.699		29.85
4	Product 3	32.64	32.929		33.75
5	Product 4	21.6	19		22.9
6	Product 5	13.68	13		13.9
7	Product 6	26.65	21.699		29.85
8	Product 7	32.64	32.929		33.75
9	Product 8	21.59	19.164		22.85
10	Product 9	21.76	17.276		22.5
11	Product 10	21.6	19		22.9
12	Product 11	32.64	32.929		33.75
13	Product 12	81.96	85.579		87.48
14	Product 13	48.96	37.299		53.5
15	Product 14	40.96	40.96		39.5
16	Product 15	65.88	68.684		64.68
17	Product 16	58.94	58.005		62.5
18	Product 17	25.23	26.672		27.5
19	Product 18	231.84	231.84		251.72
20	Product 19	122.94	122.94		131.22
21	Product 20	81.96	85.579		87.48
22
23		Low 641.85	Low 584.422	Low 0	Low 104.18
24		High 40.96	High 109.644	High 0	High 973.39
Sheet14

Sub SumHighLow()
Dim slo As Double, shi As Double, i As Long, j As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For j = 2 To 5
slo = 0
shi = 0
For i = 2 To lr
 If 14348258 = Cells(i, j).DisplayFormat.Interior.Color Then ' CF green
 slo = slo + Cells(i, j)
 ElseIf 1137094 = Cells(i, j).DisplayFormat.Interior.Color Then ' CF red
 shi = shi + Cells(i, j)
 Else
 End If
Next i
Cells(23, j) = "Low  " & slo
Cells(24, j) = "High " & shi
Next j
End Sub

 

[alansidman]

After running PQ and bring the file into Excel, Convert the Table to a range and apply the formulas shown. Keep in mind that this is an alternative to VBA.

Book7.xlsx
	A	B	C	D	E	F	G	H	I
1	Column1	V1	V2	V3	V4	Maximum	Minimum	Max Vendor	Min Vendor
2	Product 1	5.25	3.5		5.99	5.99	3.5	V4	V2
3	Product 2	26.65	21.699		29.85	29.85	21.699	V4	V2
4	Product 3	32.64	32.929		33.75	33.75	32.64	V4	V1
5	Product 4	21.6	19		22.9	22.9	19	V4	V2
6	Product 5	13.68	13		13.9	13.9	13	V4	V2
7	Product 6	26.65	21.699		29.85	29.85	21.699	V4	V2
8	Product 7	32.64	32.929		33.75	33.75	32.64	V4	V1
9	Product 8	21.59	19.164		22.85	22.85	19.164	V4	V2
10	Product 9	21.76	17.276		22.5	22.5	17.276	V4	V2
11	Product 10	21.6	19		22.9	22.9	19	V4	V2
12	Product 11	32.64	32.929		33.75	33.75	32.64	V4	V1
13	Product 12	81.96	85.579		87.48	87.48	81.96	V4	V1
14	Product 13	48.96	37.299		53.5	53.5	37.299	V4	V2
15	Product 14	40.96	40.96		39.5	40.96	39.5	V1	V4
16	Product 15	65.88	68.684		64.68	68.684	64.68	V2	V4
17	Product 16	58.94	58.005		62.5	62.5	58.005	V4	V2
18	Product 17	25.23	26.672		27.5	27.5	25.23	V4	V1
19	Product 18	231.84	231.84		251.72	251.72	231.84	V4	V1
20	Product 19	122.94	122.94		131.22	131.22	122.94	V4	V1
21	Product 20	81.96	85.579		87.48	87.48	81.96	V4	V1
22						1083.034	975.672
Table1

Cell Formulas
Range	Formula
H2,H4:H21	H2	=XLOOKUP(Table1!F2,Table1!B2:E2,Table1!$B$1:$E$1)
I2:I21	I2	=XLOOKUP(G2,B2:E2,$B$1:$E$1)
F22	F22	=SUBTOTAL(109,Table1!$F$2:$F$21)
G22	G22	=SUBTOTAL(109,Table1!$G$2:$G$21)

@xdriver

Late Edit: My apologies as this solution will not work for you. I just noticed that you are running a MAC. Power Query is not an option for MAC users.