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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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")
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
ABCD
Item1700t high
Item1350t lowi get350t low
but if its only one row so just
item 21400billyi only get the number not the name1400
 
Upvote 0
How about
Excel Formula:
=INDEX(SORT(FILTER(Lookup!C$2:D$26,Lookup!A$2:A$26=A2,"No Bids")),1,{1,2})
 
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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