minimum of an array

charijit

Board Regular
Joined
Nov 27, 2003
Messages
89
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 
Upvote 0

charijit

Board Regular
Joined
Nov 27, 2003
Messages
89
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.
 
Upvote 0

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
642
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.
 
Upvote 0

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
charijit said:
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
 
Upvote 0

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
642
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
 
Upvote 0

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
Hi ken!
The 3 is not needed because
your array is just single column
Code:
Sheet1!$C$1:$C$15
You only refer to column C.
If suppose you have this one
Code:
Sheet1!$A$1:$C$15
then you need the 3 to reffer to the third column
in the range you specified.
 
Upvote 0

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
642
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! o_O
Thanks for correcting me.... I should just read!
Ken
 
Upvote 0

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
642
Hi charijit....try what Sixth Sense was eluding to.....make sure you CSE the formula in B1...works for me perfectly!
ken
 
Upvote 0

Forum statistics

Threads
1,186,361
Messages
5,957,402
Members
438,304
Latest member
duck90

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