# Find min value in a range

#### Apple Ang

##### New Member
Hi All,
Good day!

I would like to have a macr which can help me to find the lowest price in a range then copy the value & vendor to column next to price. I can do it if the range is in column but not as below.

My data as below:
Book1
ABCD
1PartVendorPrice
2P1V10.01
3P1V20.01
4P1V30.015
5P1V40.02
6P2V10.35
7P2V50.37
8P2V60.355
9P2V30.36
10P3V71.3
11P3V51.2
12P3V41.15
Sheet1

As example, there are 4 vendor can supply part P1 with different price. V1 & V2 are the vendor with lowest prices. This macro will help me to copy the vendor & price to first row of P1.
The result should as below:
Book1
ABCDE
1PartVendorPriceSelectedVendorLowerprice
2P1V10.01V10.01
3P1V20.01V20.01
4P1V30.015
5P1V40.02
6P2V10.355V60.35
7P2V50.37
8P2V60.35
9P2V30.36
10P3V71.3V51.2
11P3V51.2
12P3V41.15
Sheet1

Can somebody help me on this?

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
What if more than one item has the lowest value?

try;
Code:
``````Sub sample()
Dim mrange As Range
Range("A2").Activate
Application.ScreenUpdating = False
Range("d1").Resize(, 2) = Array("Selected Vendor", "Lower Price")
Range("d2:e" & Range("a" & Rows.Count).End(xlUp).Row).ClearContents
start:
s = ActiveCell.Row
Do While ActiveCell.Value = ActiveCell.Offset(1).Value And ActiveCell.Offset(1) <> ""
ActiveCell.Offset(1).Activate
Loop
l = ActiveCell.Row
Set mrange = Range("c" & s & ":" & "c" & l)
For Each c In mrange
If c.Value = WorksheetFunction.Min(mrange) And c.Offset(1).Value = c.Value Then
Range("d" & s).Resize(, 2) = Array(c.Offset(, -1), WorksheetFunction.Min(mrange))
Range("d" & s + 1).Resize(, 2) = Array(c.Offset(1, -1), WorksheetFunction.Min(mrange))
ElseIf c.Value = WorksheetFunction.Min(mrange) And Range("d" & s) = "" Then
Range("d" & s).Resize(, 2) = Array(c.Offset(, -1), WorksheetFunction.Min(mrange))
End If
Next
ActiveCell.Offset(1).Activate
If ActiveCell.Offset(1) = "" Then Exit Sub
GoTo start:
Application.ScreenUpdating = True
End Sub``````

Hi Agihcam & Glenn,

Thanks to both of you. It really help me a lot.

If there are more then 2 parts with lowest prices, I hope that parts can be highlighted.

Again, thanks.

if you want a spradsheet solution:

the unique values are in column D (by using advance filter) and see the formulas in E2. copy E2 formula down .
Book3
ABCDE
1PartVendorPrice
2P1V10.01V10.01
3P1V20.01V20.01
4P1V30.015V30.015
5P1V40.02V40.02
6P2V10.35V50.37
7P2V50.37V60.355
8P2V60.355V71.3
9P2V30.36
10P3V71.3
11P3V51.2
12P3V41.15
Sheet1

if you want a spradsheet solution:

the unique values are in column D (by using advance filter) and see the formulas in E2. copy E2 formula down .
you have to invoke formula by control shift enter.
Book3
ABCDE
1PartVendorPrice
2P1V10.01V10.01
3P1V20.01V20.01
4P1V30.015V30.015
5P1V40.02V40.02
6P2V10.35V50.37
7P2V50.37V60.355
8P2V60.355V71.3
9P2V30.36
10P3V71.3
11P3V51.2
12P3V41.15
Sheet1

Replies
5
Views
385
Replies
1
Views
450
Replies
2
Views
196
Replies
12
Views
468
Replies
12
Views
293

1,203,642
Messages
6,056,511
Members
444,871
Latest member
Vishal Gupta

### 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.

### Which adblocker are you using?

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

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