excell programing?

[Mattius]

anyone any good at excel?
basically i have a table of prices for various parts from different suppliers, i want it to pick the cheapest supplier for each part and highlight it, then give me a total cost of all the cheapest parts.
is this possible?

 

[ByteJuggler]

Somewhat facetiously: Yes.

How exactly is a different matter. At worst you will definitely be able to do it with VBA, there *might* be a better/easier way but can't think of one offhand (maybe using vlookup, match, index etc etc.) Can you provide an example of the data you want to process?

 

[mark66]

Hi,

Give this ago and you should not need to resort to writing any VbA!

I've assumed all your parts are in a long list, with a column that identifies the parts, a supplier and prices. Download my example

1, First, list all the unique parts at the bottom of the list (I'll refer to this range as Lowest_Names). Put in the following array function, (type the formula without the curly brackets and press Shift+Ctrl+Enter):
{=MIN(IF(List_Parts = Lowest_Name, List_Prices, ""))}

So in the example attached, I have listed CPU, RAM and USB in cells A26: A28. I have then entereed into cell C26 the array function:
{=MIN(IF(A2:A24 = A26, C2:C24, ""))}
This returns the lowest price for CPU. Copy the formula down to return the lowest prices for the other two products.

2, Now that you have the lowest price for each part, you can use Format > Conditional Formatting to highlight the relevant rows. Use this formula in the dialog box, under "Formula is":
=(List_Price = INDEX(Range_of_Lowest_Prices, MATCH (List_Part, Range_of_Lowest_List_Prices,0)))
and apply some formatting to identify it.

Hope this helps