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

[tonywatsonhelp]

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

 

[JEC]

You could try this in C2 of sheet Prices. (office 365)

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

 

[Peter_SSs]

A couple of points:

FILTER has a built-in capacity to deal with the situation where nothing is returned so IFERROR can be omitted

=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]

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]

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]

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]

How about

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

 

[tonywatsonhelp]

Thank you Fluff,
this works perfectly
Tony

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

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)