minimum of an array

[charijit]

Dear All,
In column-A I have various items : a,b,c,d,e,r,v,a,b,d,t,h,e,r,v.
In column-B I have prices of items in Column-A : 1,3,4,5,5,6,3,4,6,7,8,3,4,5,6.
In column-C I have vendor names, who have supplied the items in Column-A : ww,ee,dd,ff,ee,ee,dd,ww,rr,zz,ss,ee,ee,rr,dd.

In a separate sheet I have listed the items nos. appearing in Column-A mentioned above. In the second column I wish the minimun price of the item to appear (since item is repeating and vendors may vary and thus their rates). In the third column I wish the vendor name corresponding to the minimun price to appear.
I seek all of your help.

 

[acw]

Hi

Assuming that your data is in sheet 1 in the range A1:C15 and the output data is in sheet 2, with the unique list of items starting in A1.

In B1 array enter the formula
=MIN(IF(Sheet1!$A$1:$A$15=Sheet2!A1,Sheet1!$B$1:$B$15,999))

In C1 enter the formula
=INDEX(Sheet1!$C$1:$C$15,SUMPRODUCT(--(Sheet1!$A$1:$A$15=Sheet2!A1),--(Sheet1!$B$1:$B$15=Sheet2!B1),ROW(Sheet1!$A$1:$A$15)))

Copy down as required.

The formula in B1 has as a default maximum 999. You will have to change this to reflect the range of your data.


Tony

 

[charijit]

Tony,
Kindly check your logic for sorting. Against b the lowest value is 3 whereas your logic for Column-B will invariably show value as 1. Thank you.

 

[ken2step]

I ran his formulas in simulation and they worked perfectly returning the b value as 3 and vendor ee. You must of entered something wrong.
Ken

btw Tony...nice formula. I needed to ad the 3 for the column argument for Indexing though.

 

[SIXTH SENSE]

Tony,
Kindly check your logic for sorting. Against b the lowest value is 3 whereas your logic for Column-B will invariably show value as 1. Thank you.

Note:

The formula is array entered
Enter them by pressing Cltrl+****+Enter
They should work

 

[ken2step]

Hi Sixth Sense...doesn't this need something...

In C1 enter the formula
=INDEX(Sheet1!$C$1:$C$15,SUMPRODUCT(--(Sheet1!$A$1:$A$15=Sheet2!A1),--(Sheet1!$B$1:$B$15=Sheet2!B1),ROW(Sheet1!$A$1:$A$15)),3)

Ken

 

[SIXTH SENSE]

Hi ken!
The 3 is not needed because
your array is just single column

Sheet1!$C$1:$C$15

You only refer to column C.
If suppose you have this one

Sheet1!$A$1:$C$15

then you need the 3 to reffer to the third column
in the range you specified.

 

[charijit]

Ken,
I may have done mistakes, but its not working after several attempts.

 

[ken2step]

Thanks Sixth Sense....I see now....when I was appling it my array used was A:C....I did not read his formula correctly....as usual! Those little details do make a difference huh!
Thanks for correcting me.... I should just read!
Ken

 

[ken2step]

Hi charijit....try what Sixth Sense was eluding to.....make sure you CSE the formula in B1...works for me perfectly!
ken