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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,978
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

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
Joined
Aug 21, 2021
Messages
802
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
52,325
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 24, 2014
Messages
2,978
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
52,325
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,182
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,978
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you Fluff,
this works perfectly :)
Tony
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,182
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,325
Office Version
  1. 365
Platform
  1. Windows
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)
 
Master Excel Bundle

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.

Forum statistics

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

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
Top