# Macro (or formula) to find the cheapest rate for each item in a column

#### tonywatsonhelp

##### Well-known Member
Hi Everyone,
I have a list of about 70,000 items in a sheet called Sheet("Prices") in Column A
I want to find and record the lowest bid price and the name of the person in column C and D

The bids are in a sheet called sheet.("Lookup")

Column A holds the items that match Sheet("Prices"). Column A,
and Column C hold the Bid Prices, and Column D the workman's Name.

So what I need to do is find the lowest price offered for every line, if no bid was made then I want the words "No Bids".

So lets say Sheet ("Prices") Cell A2 = "Item 1"

Then I need a macro (or formula) to goto Sheets("Lookup") Column "A"
and find all rows that match the name "Item 1"

Then from those that match, find the lowest price in column "C" then copy that Price and the name in the same row column D to Sheets("Prices"). Column C2 & D2 and so on down every row

I've been struggling with this for a few weeks now and only have until tomorrow to get it sorted so any ideas would be greatly appreciated.
Thanks
Tony

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### JEC

##### Well-known Member
You could try this in C2 of sheet Prices. (office 365)

Excel Formula:
``=IFERROR(INDEX(SORT(FILTER(Lookup!C2:D26,Lookup!A2:A26=A2),,1),1),"No Bids")``

#### Peter_SSs

##### MrExcel MVP, Moderator
A couple of points:

FILTER has a built-in capacity to deal with the situation where nothing is returned so IFERROR can be omitted
Excel Formula:
``=INDEX(SORT(FILTER(Lookup!C\$2:D\$26,Lookup!A\$2:A\$26=A2,"No Bids"),,1),1)``

find the lowest price in column "C" then copy that Price and the name
What if there are two or more equal lowest prices for a particular item?

#### tonywatsonhelp

##### Well-known Member
What if there are two or more equal lowest prices for a particular item?

Thanks peter, i meant to say that wont matter as long as i get one

#### Peter_SSs

##### MrExcel MVP, Moderator

Thanks peter, i meant to say that wont matter as long as i get one
OK, so do either/both of the formulas (with adjusted ranges) work for you?

#### tonywatsonhelp

##### Well-known Member
What if there are two or more equal lowest prices for a particular item?

Thanks peter, i meant to say that wont matter as long as i get one

Hi Everyone,

This code works great unless theres only one price then it only returnc column C not cokumn D as well any ideas how i can get around this?

=INDEX(SORT(FILTER(Lookup!C\$2:D\$26,Lookup!A\$2:A\$26=A2,"No Bids"),,1),1)

So If I have
 A B C D Item1 700 t high Item1 350 t low i get 350 t low but if its only one row so just item 2 1400 billy i only get the number not the name 1400

#### Fluff

##### MrExcel MVP, Moderator

Excel Formula:
``=INDEX(SORT(FILTER(Lookup!C\$2:D\$26,Lookup!A\$2:A\$26=A2,"No Bids")),1,{1,2})``

#### tonywatsonhelp

##### Well-known Member
Thank you Fluff,
this works perfectly
Tony

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

#### Peter_SSs

##### MrExcel MVP, Moderator
Thank you Fluff,
this works perfectly
Tony
If there are no bids for an item, doesn't that return an error in the name column?

My suggestion to deal with the single price issue & avoid the #REF! error would be to add this near the end of my previously suggested formula.

=INDEX(SORT(FILTER(Lookup!C\$2:D\$17,Lookup!A\$2:A\$17=A2,"No Bids"),,1),1,0)

Replies
3
Views
93
Replies
2
Views
146
Replies
4
Views
294
Replies
5
Views
230
Replies
5
Views
235

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,259
Messages
5,769,082
Members
425,516
Latest member
Melzebu

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

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